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

Sailors

Reserves

Boats

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!

CodePudding user response：

The approach I'd take would be to select all sid values from table_reserves that have a distinct count of bid equal to the rowcount of the boats table. I don't have your schema prepared, but it'd be something like this:

with cte AS (

    SELECT 
    sid, 
    COUNT(DISTINCT bid) AS unique_boat_count

    FROM reserves r

    HAVING COUNT(DISTINCT bid) = (
        SELECT COUNT(*) 
        FROM boats
        )
)       
select 

s.sname

FROM cte 

INNER JOIN sailors s
    ON s.sid = cte.sid

CodePudding user response：

Start with this:

SELECT s.sid
FROM Sailors
INNER JOIN Reserves r on r.sid = s.sid
GROUP BY s.ID
HAVING COUNT(DISTINCT r.bid) = 
    (SELECT count(distinct bid) from boats)

What we're doing is matching the count of total unique boats registered with the count of total votes 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.