Results 1 to 4 of 4
  1. #1
    Join Date
    May 2012
    Posts
    2

    Unanswered: Left join with where clause does not work!

    Hi!

    I have tables A and B.

    Table A has data, but B may have or not.
    I have to get -- at least! -- the A table data.

    It works when I dont have the where clause:

    select * from A left outer join B on A.IDUser = B.IDUser

    But it doesnt when I try to use the WHERE clause:

    select * from A left outer join B on A.IDUser = B.IDUser
    where A.IDUser = 2

    Can anyone help me?
    Thanks in advance!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The WHERE clause is evaluated after the join. Try this instead:

    select *
    from A
    left outer join B
    on A.IDUser = B.IDUser
    AND A.IDUser = 2
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Hang on a second...

    Your query should have worked, since your filter was on A rather than the left-joined B table.


    Run this:
    select IDUser, count(*)
    from A
    group by IDUser

    Are you sure you have records with that particular ID?
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    May 2012
    Posts
    2

    Ops... My bad...

    Dear Blindman,
    Thanks for your response.
    You are right: I dont have enough data.
    I just realize it now...

    Thanks!

    Libânia

Posting Permissions

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