Join question

Scotty2024 · #1 (**permalink**) 03-12-11, 14:11

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.

r937 · #2 (**permalink**) 03-12-11, 21:32

Quote:

Originally Posted by Scotty2024

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

Scotty2024 · #3 (**permalink**) 03-12-11, 22:27

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?

r937 · #4 (**permalink**) 03-12-11, 22:47

Quote:

Originally Posted by Scotty2024

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

Internet Services

Web Development

Digital Marketing

Consumer Tech