Find the sailors that have been on EVERY boat

Question

I don't know how to explain the problem in a generic way so i'll post the specific case:

i have 3 tables:

Sailors:

S(ids, names, rating, age)

Boats:

B(idb, nameb, color)

Bookings:

Bo(ids, idb, date)

i have to write a query that finds all the sailors who have booked EVERY boat.

Even if i posted a specific case i'd like a generic answare that can be applied to every problem of tha same kind.

thank you in advance.

Answer 1

You can get the sailors's ids who have booked every boat with this query:

select ids
from bookings
group by ids
having count(distinct idb) = (select count(*) from boats)

So use it either with the operator IN:

select * from sailors
where ids in (
    select ids
    from bookings
    group by ids
    having count(distinct idb) = (select count(*) from boats)
)

or join it to sailors:

select s.*
from sailors s 
inner join (
    select ids
    from bookings
    group by ids
    having count(distinct idb) = (select count(*) from boats)
) t on t.ids = s.ids