Results 1 to 4 of 4

Thread: Join question

  1. #1
    Join Date
    Nov 2008
    Posts
    8

    Question Unanswered: Join question

    Hello. I'm trying to create a query to join on two tables.

    Table1 has id and name. Table2 has userID and itemID.

    Table1 id and table2 userID are the same. For a specific itemID I want to select all names from table1 where the userID in table2 does not exists.

    For example, here is some test data.

    Table1
    1 Bill
    2 Bob
    3 Joe

    Table2
    1 1
    2 1
    3 2

    If I wanted to get all names where the userID wasn't listed for itemID 1, it would return Joe. Similarly if I wanted to get all names where the userID wasn't listed for itemID 2, it would return Bob and Joe. Finally, if I wanted to get all names where the itemID was not equal to 1 or 2, it would return Bill, Bob, and Joe.

    How do I join the two tables to do this? Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Scotty2024 View Post
    For a specific itemID I want to select all names from table1 where the userID in table2 does not exists.
    presumably you mean "where the userID in table2 does not exists in Table1 as id", correct?
    Code:
    SELECT Table2.userid
      FROM Table2
    LEFT OUTER
      JOIN Table1
        ON Table1.id = Table2.userid
     WHERE Table2.itemID = 937
       AND Table1.id IS NULL
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2008
    Posts
    8
    Hi r937.

    I think the query you provided is close. Except I am looking to get the names from Table1 where a the user is not connected to a given itemID in Table2.

    Using the example data in my first post, if I wanted to see what names weren't attached to Table2.itemID = 1 it would return Joe. Or if I wanted to see which names weren't attached to Table2.itemID = 2 it would return Bob and Joe. Last, if I wanted to see which names weren't attach to anything other than Table2.itemID != 1 OR Table2.itemID != 2 then it would return all names.

    Does this make sense?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Scotty2024 View Post
    Except I am looking to get the names from Table1 where a the user is not connected to a given itemID in Table2.
    so, basically, the other way around
    Code:
    SELECT Table1.name
      FROM Table1
    LEFT OUTER
      JOIN Table2
        ON Table2.userid = Table1.id
       AND Table2.itemID = 937
     WHERE Table2.userid IS NULL
    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
  •