sql - Converting From NOT EXISTS to NOT IN -

sql - Converting From NOT EXISTS to NOT IN -

i have 3 tables:

sailor (sname, rating); boat (bname, color, rating); reservation (sname, bname, weekday, start, finish);

in order list of sailors have reserved every reddish boat, have:

select s.sname sailor s not exists( select * boat b b.color = 'red' , not exists ( select * reservation r r.bname = b.bname , r.sname = s.sname));

i need rewrite query not in instead of not exists. here's have far:

select s.sname sailor s s.sname not in (select s2.sname sailor s2 s2.sname not in (select r.sname reservation r r.bname not in ( select b.bname boat b b.color <> 'red' )));

this, however, returns list of sailors have reserved reddish boat (not of them). i'm having great difficulty checking whether or not name in list has reserved every boat (i cannot utilize count()).

any help appreciated

inorder list of sailors have reserved every boat. i'll utilize script

solution 1:

;with k ( select b.sname,count(distinct a.bname) coun boat inner bring together reservation b on a.bname = b.bname grouping b.sname ) select k.sname k coun = (select count(*) boat b)

solution 2:

select s.sname sailor s s.sname not in (select distinct a.sname (select s.sname, b.bname sailor s cross bring together boat b b.color = "red") a.sname + a.bname not in (select r.sname + r.bname reservation r r.sname not null , r.bname not null));

sql database relational-database relational-division