Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2012
    Posts
    3

    Question Unanswered: Basic question, Left Join acting like Inner Join

    I don't get why this isn't working:

    table1:
    table1ID
    table2IDs

    table2:
    table2ID

    I want all the records from table2, including the rows that where the table2ID is not in any table2IDs from table1.

    SELECT
    table2.table2ID
    FROM
    table2
    LEFT JOIN table1
    ON table2.table2ID = table1.table2IDs;

    The above query only returns records from talbe2 that have a corresponding value in table2IDs, just like an INNER JOIN.

    I'm probably missing something simple here...

  2. #2
    Join Date
    Jan 2012
    Posts
    3
    Bah. Figured it out.

    I left out a part where i was checking criteria on table1, and it won't return an "= NULL" row, because there are no NULL records in table2, only in the result set I'm looking for.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what were the table1 criteria you left out? can you show the final query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2012
    Posts
    3
    Sure. Basically there are 2 additional columns in both tables that determine whether the row is valid or not. We'll call these columns X, and I was looking for a value of 0, so:

    SELECT
    table2.table2ID
    FROM
    table2
    LEFT JOIN table1
    ON table2.table2ID = table1.table2IDs
    WHERE table2.X = 0
    AND table1.X = 0;

    What I was really looking for in this, were the rows in table2 that were not assigned in table1 (I guess I should note I pulled in the ID from table1 as well so I could sort for the nulls), but the join + checking for X=0 eliminated those rows, so taking "AND table1.X = 0" out of the query above made it work.

    It's possible I got some invalid rows where the IDs matched, but since I was only looking for the nulls, that was fine.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    try this --
    Code:
    SELECT table2.table2ID
      FROM table2
    LEFT OUTER
      JOIN table1
        ON table1.table2IDs = table2.table2ID
       AND table1.X = 0
     WHERE table2.X = 0
    see the difference? (no, i'm not talking about the word OUTER -- that's optional, and i just always write it out of habit)

    with the table1 condition in the ON clause, it behaves differently, too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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