Select from a table who is all available in other tables

I’m currently working on some sql tables, Here is the tables below in an image.

Sailors

sid	sname	rating	age
22	Dustin	7	45.0
29	Brutus	1	33.0
31	Lubber	8	55.5
32	Andy	8	25.5
58	Rusty	10	35.0
64	Horatio	7	35.0
71	Zorba	10	16.0
74	Horatio	9	35.0
85	Art	3	25.5
95	Bob	3	63.5

Reserves

sid	bid	day
22	101	10/10/98
22	102	10/10/98
22	103	10/8/98
22	104	10/7/98
31	102	11/10/98
31	103	11/6/98
31	104	11/12/98
64	101	9/5/98
64	102	9/8/98
74	103	9/8/98

Boats

bid	bname	bcolor
101	Interlake	blue
102	Interlake	red
103	Clipper	green
104	Marine	red

So, I want to select a person in table Sailors who had reserved all boats from table boats, which is Dustin.

My first thought on the code is

SELECT s.sname 
FROM Sailors s, Reserves r, Boats b 
WHERE s.sid=r.sid AND r.bid=b.bid AND b.color='red' AND b.color='green' AND b.color='blue';

But, the function above will keep increasing if I add the new boats with a different colors.

My question is, are there any shorter way to call the person without stating the boat one by one? Is there any shorter version to do this?

Thank you!

Answer

Start with this:

SELECT r.sid
FROM Reserves r
GROUP BY r.sid
HAVING COUNT(DISTINCT r.bid) = 
    (select count(distinct bid) from boats)

What we’re doing is matching the count of total unique boats registered for each sailor with the count of total boats possible. If you need more than just the sid value you can JOIN back to the Sailors table again.

Do NOT use the old FROM A,B join syntax for this, as you did in the original question. That syntax has been obsolete for more than 25 years now. Today, you should pretty much always specify the JOIN keyword.