Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2003
    Posts
    4

    Post Unanswered: left outer join with > 1 condition

    Hi

    I know the following is not correct, but anyone know a solution?

    SELECT tableA.fldA, tableA.fldB, tableA.fldC
    FROM tableA LEFT OUTER JOIN tableB
    ON (tableA.fldA=tableB.fldA AND tableA.fldB=tableB.fldB)
    WHERE tableB.fldA is NULL AND tableB.fldB is NULL.

    What I am trying to do it to do a left outer join on tableA with condition (tableA.fldA, tableA.fldB) NOT IN SELECT tableB.fldA, tableB.fldB FROM tableB

    One of the solution I can come up with is to do a union join which is not good for efficiency side.

    any idea

    thanks
    Pip

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why do you say it is not correct?

    rudy
    http://r937.com/

  3. #3
    Join Date
    Jul 2003
    Posts
    4
    (1) Doesn't outer join only apply 1 condition (ON tableA.fldA = tableB.fldA).. what I was trying to do is to apply on 2 conditions (ON tableA.fldA=tableB.fldA AND tableA.fldB=tableB.fldB).

    (2) Doesn't subquery is only for 1 fld.

    SELECT somthing
    FROM sometable
    WHERE (SELECT fld FROM othertable)

    thanks
    Pip

    Originally posted by r937
    why do you say it is not correct?

    rudy
    http://r937.com/

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ON clause can have multiple conditions -- try it

    yes, usually a subquery is required to return only one column

    sometimes only one column, and only one value (row)

    but in this case you don't need a subquery


    rudy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2003
    Posts
    4
    I tried it, but it doesn't work


    Originally posted by r937
    ON clause can have multiple conditions -- try it

    yes, usually a subquery is required to return only one column

    sometimes only one column, and only one value (row)

    but in this case you don't need a subquery


    rudy

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    then there's something wrong with your query

    sql server does support it

    can you show your query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Outer joins are not limited to one condition, and you can return as many fields as you want in a subquery.

    SELECT tableA.fldA,
    tableA.fldB,
    tableA.fldC
    FROM tableA
    WHERE not exists
    (select *
    from tableB
    where tableA.fldA=tableB.fldA AND tableA.fldB=tableB.fldB)

    blindman

  8. #8
    Join Date
    Jul 2003
    Posts
    4
    Thanks.... I found out that there are some problems on the data, not the query. That's why I didn't get the expected result.

    Pip




    Originally posted by blindman
    Outer joins are not limited to one condition, and you can return as many fields as you want in a subquery.

    SELECT tableA.fldA,
    tableA.fldB,
    tableA.fldC
    FROM tableA
    WHERE not exists
    (select *
    from tableB
    where tableA.fldA=tableB.fldA AND tableA.fldB=tableB.fldB)

    blindman

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    and you can return as many fields as you want in a subquery
    not in all cases

    in the NOT EXISTS example you gave, the subquery actually returns nothing, because NOT EXISTS is either true or false if a row is found, but no columns are actually returned

    another example where you cannot return more than one value is called a scalar subquery, e.g.

    select foo, bar
    from sometable
    where qux =
    ( select max(baz)
    from someothertable )

    rudy

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    r937,

    My code was meant as a solution to the problem, not an example of how to return multiple columns in a subquery. Perhaps I should have included an explanation of the code, but I was in a rush.

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •