EXCEPT command - Find sailors who’ve reserved all boats

Question

I am reading a textbook and I do not understand this query: Find sailors who’ve reserved all boats.

We have 3 tables:
1. Sailors: sid, sname, rating, age (primary: sid)
2. Boats: bid (primary: bid)
3. Reserves: sid, bid, day (primary, sid, bid, day) (sid is foreign key to sailors, bid is foreign key to boats)

For the query, Find sailors who’ve reserved all boats, the answer given is:

SELECT  S.sname
FROM  Sailors S
WHERE  NOT EXISTS 
          ((SELECT  B.bid
             FROM  Boats B)
            EXCEPT
             (SELECT  R.bid
              FROM  Reserves R
              WHERE  R.sid=S.sid))

My questions are:

Is there something wrong with the above query? When I put it into MySQL Workbench, it shows I have syntax error with the EXCEPT
Beside the solution given by the book above, is there any other way to do the query: Find sailors who’ve reserved all boats

Thank you,

`except` is not valid in MySQL. You can use it in SQL Server. — Vamsi Prabhala, Sep 01 '16 at 18:43
And you could even rewrite it into `NOT IN()` : http://stackoverflow.com/q/26697519/905902 — wildplasser, Sep 01 '16 at 18:57

kjmerf · Accepted Answer · 2016-09-01T19:19:24.740

You can do this using COUNT and CONCAT. This query selects the sailors that are paired (in the Reserves table) with each boat in the boat table:

SELECT Sailors.name
FROM Sailors INNER JOIN
(SELECT Reserves.sid
 FROM Reserves
 GROUP BY Reserves.sid
 HAVING COUNT(DISTINCT CONCAT(Reserves.sid, Reserves.bid)) =
 (SELECT COUNT(DISTINCT Boats.bid)
  FROM Boats)) sub
ON Sailors.sid = sub.sid

Tested here: http://sqlfiddle.com/#!9/82005/2

score 3 · Answer 2 · answered Apr 10 '17 at 09:10

Sometime i have seen that EXCEPT don't work due to incompatibility with the installed version of MySql. Following is the query which can be used in place of yours using NOT EXISTS.

SELECT S.sname 
FROM Sailors S
WHERE NOT EXISTS (SELECT B.bid 
                  FROM Boats B
                  WHERE NOT EXISTS(SELECT R.bid
                                   FROM Reserves R
                                   WHERE R.bid = B.bid
                                         AND R.sid = S.sid));

score 0 · Answer 3 · edited Apr 19 '18 at 08:37

0

The following query should be used:

SELECT sname FROM Sailors s ,Reserves r WHERE  s.sid=r.sid AND r.bid=ALL(SELECT b.bid FROM boats b);

edited Apr 19 '18 at 08:37

Rann Lifshitz

4,040
4
22
42

answered Apr 19 '18 at 07:42

swathi amu

9
1

This would not work. WHERE clauses look at each record individually. This [r.bid=ALL (SELECT b.bid FROM boats b);] is saying this individual r.bid is equal to every b.bid in boats. This is always false for 2 or more unique boats. – Aaron Elliot Jun 29 '21 at 01:41

EXCEPT command - Find sailors who’ve reserved all boats

3 Answers3