I have three tables in MySQL database. First table contains users, second table contains items. Below is the structure of those two.
users
------
userid (int)
username (varchar)
items
------
itemid (int)
name (varchar)
The third table is a join table.
user_items
----------
userid (int)
itemid (int)
I would like to have a query which returns me the list of users and the items not assigned to them.
In example I have following users
userid username
1 john
2 tim
3 mark
Also I have following items
itemid name
1 book
2 pen
3 backpack
In the join table I have
userid itemid
1 1
1 3
2 1
2 2
2 3
3 2
As a result I would like to have list of items not owned by users, like:
userid itemid
1 2
3 1
3 3
What would be the best query to get such result. I am trying with some left joins, left outer joins, not in etc. but without success.
Edit 1: I have tried so far with following query:
SELECT con.userid, i.itemid FROM items i
LEFT JOIN (
SELECT u.id as userid, ui.itemid
FROM users u
INNER JOIN user_items ui ON u.userid = ui.itemid
) con ON i.itemid = con.itemid
WHERE con.itemid IS NULL
As a result I would like to have list of items not owned by users
You would typically cross join the users and products to generate all possible combinations, then filter out associations that already exist in the bridge table:
select u.userid, p.itemid
from users u
cross join items i
where not exists (
select 1 from user_items ui where ui.userid = u.userid and ui.itemid = i.itemid
)
For performance, you want an inde on user_items(userid, itemid) (it should already be there if you have a unique constraints on these columns).
We could also express the logic with a anti-left join:
select u.userid, p.itemid
from users u
cross join items i
left join user_items ui on ui.userid = u.userid and ui.itemid = i.itemid
where ui.userid is null