CODEWITHSUNDEEP
sqlsql-serverrelational-database
zabuuq
zabuuq

Reputation: 47

Query where relationship does not exist between three tables

I have three tables (Users, Items, UserItems) where UserItems is a many-to-many relationship between Users and Items. What I need is a query that gets all Items that a User does not have.

I've created a SQL Fiddle example with some fake data here: http://sqlfiddle.com/#!6/a6856/1

CREATE TABLE Users ( userID INT, firstName NVARCHAR(50) )
CREATE TABLE Items ( itemID INT, itemName NVARCHAR(50) )
CREATE TABLE UserItems ( userID INT, itemID INT )

INSERT INTO Users ( userID, firstName )
VALUES ( 1, 'Jack' ), ( 2, 'Jill' ), ( 3, 'John' ), ( 4, 'Jane' );

INSERT INTO Items ( itemID, itemName )
VALUES ( 1, 'Bucket' ), ( 2, 'Water' ), ( 3, 'Cast' );

INSERT INTO UserItems ( userID, itemID )
VALUES ( 1, 1 ), ( 1, 2 ), ( 1, 3 ), ( 2, 3 );

Desired Results Users and what items they DO NOT have:

---------------------------------
| userID | firstName | itemName |
|--------|-----------|----------|
| 2      | Jill      | Bucket   |
| 2      | Jill      | Water    |
| 3      | John      | Bucket   |
| 3      | John      | Water    |
| 3      | John      | Cast     |
| 4      | Jane      | Bucket   |
| 4      | Jane      | Water    |
| 4      | Jane      | Cast     |
---------------------------------

NOTE: Jack does not need to be in the results because he has all three items.

Upvotes: 0

Views: 1404

Answers (4)

Craig K
Craig K

Reputation: 56

NOT EXISTS seems like a perfect fit here

SELECT
    usr.userID
    , usr.firstName
    , itm.itemName
FROM 
    Users usr
    , Items itm
WHERE NOT EXISTS
(
    SELECT *
    FROM UserItems usrItm
    WHERE 
        usrItm.itemID = itm.itemID
        AND usrItm.userID = usr.userID
)
ORDER BY
    usr.userID
, itm.itemID

Upvotes: 0

Mircea Matei
Mircea Matei

Reputation: 601

SELECT u.userId, u.firstName, i.itemId, i.itemName
  FROM users u CROSS JOIN items i
  LEFT OUTER JOIN  useritems ui
  ON u.userID = ui.userId AND i.itemId = ui.itemId
  WHERE ui.itemId IS NULL
  ORDER BY 1, 3;

Upvotes: 1

DeadZone
DeadZone

Reputation: 1680

This looks like a good use case for the EXCEPT operator.

SELECT      U.UserID, I.itemID
FROM        Users U 
CROSS JOIN  Items I -- Gets a set of all users and all items
EXCEPT
SELECT      UI.UserID, UI.ItemID
FROM        UserItems UI    -- Removes the items that a user has.

Upvotes: 1

Nolan Shang
Nolan Shang

Reputation: 2328

select * 
from Users as u inner join Items as i on 1=1
where not exists(select 0 from UserItems as ui where ui.userID=u.userID and i.itemID=ui.itemID)
order by userID,firstName,itemID

+--------+-----------+--------+----------+
| userID | firstName | itemID | itemName |
+--------+-----------+--------+----------+
| 2      | Jill      | 1      | Bucket   |
| 2      | Jill      | 2      | Water    |
| 3      | John      | 1      | Bucket   |
| 3      | John      | 2      | Water    |
| 3      | John      | 3      | Cast     |
| 4      | Jane      | 1      | Bucket   |
| 4      | Jane      | 2      | Water    |
| 4      | Jane      | 3      | Cast     |
+--------+-----------+--------+----------+

Upvotes: 4

Related Questions