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.

Find sailors for which there isn't a boat for which there isn't a booking record for that sailor. — GSerg, Nov 15 '20 at 17:16

score 2 · Accepted Answer · answered Nov 15 '20 at 17:18

2

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

answered Nov 15 '20 at 17:18

forpas

160,666
10
38
76

This should work very well on test data, but should be much slower than a `not exists` on real data. – GSerg Nov 15 '20 at 17:22
GSerg can you be more specific? – Guglielmo La Mastra Nov 15 '20 at 17:24
@GSerg can you post a solution with NOT EXISTS? – forpas Nov 15 '20 at 17:25
@GuglielmoLaMastra https://stackoverflow.com/a/35212957/11683, https://stackoverflow.com/q/5264658/11683, many others. – GSerg Nov 15 '20 at 17:25
@GSerg I mean solution applicable to this requirement. – forpas Nov 15 '20 at 17:28

score 0 · Answer 2 · answered Nov 15 '20 at 18:29

0

You can use sum with in:

select * from sailors s1 group by ids having (select sum(idb in (select b2.idb from bookings b2 where b2.ids = s1.id)) from boats) = (select count(*) from boats)

answered Nov 15 '20 at 18:29

Ajax1234

69,937
8
61
102

Find the sailors that have been on EVERY boat

2 Answers2