Converting From Not Exists To Not In

I have three tables: sailor (sname, rating); boat (bname, color, rating); reservation (sname, bname, weekday, start, finish); In order to get a list of sailors who have re

Solution 1:

This is funny; you can maintain the syntactic structure (correlated subqueries) while replacing NOT EXISTS ( ... ) by IN ( ...) :

SELECT s.sname from sailor s
WHERE13NOTIN (
    SELECT13FROM boat b
    WHERE b.color ='red'AND42NOTIN (
        SELECT42from reservation r
        WHERE r.bname = b.bname
        AND r.sname = s.sname
       )
    );

Solution 2:

Inorder to get a list of sailors who have reserved every boat. I'll use this script

Solution 1:

 ;WITH k AS 
    (
    SELECT b.sname,COUNT(distinct a.bname) coun FROM boat a
    INNER JOIN reservation b 
        on a.bname = b.bname
    GROUPBY b.sname
    )
    SELECT k.sname FROM k WHERE coun = (select COUNT(*) FROM boat AS b)

Solution 2:

SELECT s.sname
FROM   sailor AS s
WHERE  s.sname NOTIN (SELECTDISTINCT a.sname
                       FROM   (SELECT s.sname,
                                      b.bname
                               FROM   sailor AS s
                                      CROSS JOIN boat AS b
                               WHERE  b.color = "Red") a
                       WHERE  a.sname + a.bname 
                                            NOTIN (SELECT r.sname + r.bname
                                                    FROM   reservation AS r
                                                    WHERE  r.sname ISNOT NULL
                                                            AND r.bname ISNOT NULL));