Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jul 2003
    Posts
    70

    Unanswered: CASE statement in a JOIN

    Hi,
    I have two tables TABLE_A and TABLE_B

    TABLE_A has rows like this:
    PROJECT_ID TASK_ID TASK_NAME
    1 100 One Hundred
    1 110 One Hundred Ten
    1 120 One Hundred Twenty
    2 200 Two Hundred
    3 300 Three Hundred
    3 310 Three Hundred Ten


    TABLE_B has rows like this:
    PROJECT_ID TASK_ID AMOUNT
    1 100 1000
    1 110 1100
    2 NULL 2000
    3 300 3000


    I want to inner join TABLE_A and TABLE_B such that if TASK_ID is available in TABLE_B, then join should happen on TASK_ID (on TABLE_A.TASK_ID=TABLE_B.TASK_ID), if TASK_ID is not available the join should happen on PROJECT_ID.

    For example for PROJECT_ID=2, there is no TASK_ID in TABLE_B (in this situation the join should be ON PROJECT_ID)

    How can we do a CASE like situation here?


    Thanks in advance
    qA

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Does this work?
    Code:
    ON ft1.field1 = Coalesce(t2.field1)
    OR ft1.field2 = Coalesce(t2.field2)
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I have never seen coalesce used with 1 argument.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oops... I have forgotten where I was coming from with that now...

    Remove the coalesces and it might work..?
    (*confused*)
    George
    Home | Blog

  5. #5
    Join Date
    Dec 2002
    Posts
    50
    You can do it the way that georgev mentioned (without the coalesces), but reqardless of how you do this, you are almost surely going to have duplication in your result set. Any place that you have more than one record in TABLE_A with the same PROJECT_ID and record(s) in TABLE_B with that PROJECT_ID and a null TASK_ID, you are going to have duplication of the values in TABLE_B.

  6. #6
    Join Date
    Jul 2003
    Posts
    70
    I agree. If I use OR clause here, it would give lots of duplicates. Any other suggestions??

  7. #7
    Join Date
    Feb 2007
    Posts
    62
    on (TABLE_A.TASK_ID=TABLE_B.TASK_ID or ( (TABLE_A.TASK_ID<>TABLE_B.TASK_ID or TABLE_B.TASK_ID is null) and
    TABLE_A.PROJECT_ID=TABLE_B.PROJECT_ID)
    )
    ?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    look ma, no dupes:
    Code:
    select TABLE_A.PROJECT_ID
         , TABLE_A.TASK_ID
         , TABLE_A.TASK_NAME
         , TABLE_B.PROJECT_ID
         , TABLE_B.TASK_ID
         , TABLE_B.AMOUNT
      from TABLE_A
    inner
      join TABLE_B
        on TABLE_B.TASK_ID = TABLE_A.TASK_ID
    union
    select TABLE_A.PROJECT_ID
         , TABLE_A.TASK_ID
         , TABLE_A.TASK_NAME
         , TABLE_B.PROJECT_ID
         , TABLE_B.TASK_ID
         , TABLE_B.AMOUNT
      from TABLE_A
    inner
      join TABLE_B
        on TABLE_B.PROJECT_ID = TABLE_A.PROJECT_ID
     where not exists
           ( select 937
               from TABLE_B
              where TASK_ID = TABLE_A.TASK_ID )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Aug 2007
    Posts
    17
    select TABLE_A.PROJECT_ID
    , TABLE_A.TASK_ID
    , TABLE_A.TASK_NAME
    , TABLE_B.PROJECT_ID
    , TABLE_B.TASK_ID
    , TABLE_B.AMOUNT
    from TABLE_A
    inner
    join TABLE_B
    on TABLE_B.TASK_ID = TABLE_A.TASK_ID
    union
    select TABLE_A.PROJECT_ID
    , TABLE_A.TASK_ID
    , TABLE_A.TASK_NAME
    , TABLE_B.PROJECT_ID
    , TABLE_B.TASK_ID
    , TABLE_B.AMOUNT
    from TABLE_A
    inner
    join TABLE_B
    on TABLE_B.PROJECT_ID = TABLE_A.PROJECT_ID
    where not exists
    ( select *
    from TABLE_B
    where TASK_ID = TABLE_A.TASK_ID )

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is it me, or is that identical to r937's post, except you changed the 937 to an asterix..?
    George
    Home | Blog

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    i love it when my answers are secretly adopted by people

    by the way, it's "asterisk" -- asterix was a french comic book character

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

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    That might be why I spell it that way then
    Would I be correct in assuming that
    Code:
    IF EXISTS(SELECT 1 FROM MyTable)
    Is more efficient than
    Code:
    IF EXISTS(SELECT * FROM MyTable)
    Assuming that there is more than one column in the table?
    George
    Home | Blog

  13. #13
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    SELECT with EXISTS was optimized in SQL 2000 (I believe). It does not really pull back all of the columns as was the case I believe in SQL 7. I stopped eradicating it from code a while ago.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Well a large portion of my work is still on 6.5
    I assumed that it would only ever need to return a single row - but since 6.5 doesn't have the TOP function, I assumed it returned the entire resultset.

    Lots of assumptions, eh?
    George
    Home | Blog

  15. #15
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I believe exists exits once it finds a nonqualifying row while NOT EXISTS will scan the whole result set.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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