Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2004
    Posts
    4

    Unanswered: SQL statement eith left outer join

    Hello,

    I have a query:

    Select a.ID as ID1, b.ID as ID2 from Table1 a
    left outer join Table2 b ON b.AID=a.ID AND b.deleted=0


    So, when b.deleted=1, I get a.ID as ID1 and null as ID2

    I need to put "b.deleted=0" into WHERE part, like:

    Select a.ID as ID1, b.ID as ID2 from Table1 a
    left outer join Table2 b ON b.AID=a.ID
    WHERE b.deleted=0

    , but then I get no results, when b.deleted=1.

    Please, help. I have no way out - WHERE part and that's it (because a select is made dynamicly).

  2. #2
    Join Date
    Feb 2004
    Posts
    126
    Maybe this will help, there may be a better way to do this, but in a similar situation, I just use the CASE statement.

    SELECT a.ID as ID1, CASE WHEN b.deleted = 0 THEN b.ID ELSE NULL END
    FROM etc....

    (I think I have the proper syntax for CASE, otherwise check Books Online).

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You aren't making a lot of sense here, laimaj. You are filtering only records from table2 where [deleted] = 0, and then wondering why the value Table2.ID returns NULLs?

    What do you WANT to show for Table2.ID when Table2.deleted = 1?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Oct 2003
    Posts
    84
    SELECT A.ID AS ID1,
    CASE B.Deleted WHEN 0 THEN B.ID ELSE NULL END AS ID2
    FROM Table1 A
    LEFT JOIN Table2 B ON A.ID = B.ID;

Posting Permissions

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