Stumped on this "simple" MySQL query

Databases
1

This is for a gift exchange application, similar to a Secret Santa or a Kris Kringle. Here’s my database diagram.

You have users and you have giftlists. A user can join many giftlists, and a giftlist can have many users joined to it, so there’s a membership table to handle the many-to-many relationship.

I need a “lists-I-can-join” query that will show all the giftlists available to a given user to join. A giftlist is available to join only if the user is not already joined to it. So the query needs to produce a table with one row for each giftlist that the given user is not already a member of, and I just can’t figure out how.

I got something like this:

SELECT giftlist.id, giftlist.name, giftlist.gifts, giftlist.price
FROM giftlist
INNER JOIN membership ON giftlist.id = membership.giftlist_id
WHERE membership.user_id !=5 (5 is the given user id)

That doesn’t work because it returns multiple rows for each giftlist, one per each user that is a member, and it excludes only the rows with the given user id, leaving all the other rows with the other user ids.

If I use GROUP BY, that does consolidate the giftlist rows alright, but it still leaves the giftlist rows that have as members the given user id and other users.

Your help is much appreciated, and I hope I’ve described the issue clearly enough. Let me know if you need any clarification.

2

SELECT giftlist.id , giftlist.name , giftlist.gifts , giftlist.price FROM giftlist LEFT OUTER JOIN membership ON membership.giftlist_id = giftlist.id AND membership.user_id = 5 WHERE membership.giftlist_id IS NULL

1 Like
3

Thank you! I now see that is a general way to find records in a table that are not in a given relationship – customers that have made no purchases this month, for example, or students who have net enrolled for this term.