in my mysql database i have the following tables:
FACULTY (fid int, fname varchar(25), deptid int, primary key(fid))
CLASS (name varchar(4),meets_at varchar(9),room varchar(4), fid int,primary key (name), foreign key (fid) references faculty (fid))
I want to select the names of faculties who go to all the rooms. I tried using following :
SELECT DISTINCT F.FNAME
FROM FACULTY F
WHERE NOT EXISTS (( SELECT *
FROM CLASS C
EXCEPT
(SELECT C1.ROOM
FROM CLASS C1
WHERE C1.FID=F.FID)));
and got the following error:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXCEPT
also tried with:
SELECT DISTINCT F.FNAME
FROM FACULTY F
LEFT JOIN CLASS C ON C.FID = F.FID
WHERE C.FID IS NULL
and got "Empty Set" even when in my database there is a faculty who goes to all the rooms.
When you use except the two table must be compatible, try this :
SELECT DISTINCT F.FNAME
FROM FACULTY F
WHERE NOT EXISTS (
( SELECT ROOM
FROM CLASS C)
EXCEPT
(SELECT C1.ROOM
FROM CLASS C1
WHERE C1.FID=F.FID)
);
EDIT
The question was tagged to sql server so I gave the answer keeping that in mind, for mysql use this :
SELECT FID, COUNT(*) FROM
(
(SELECT DISTINCT f.fname, f.fid, c1.room
FROM faculty f
JOIN class c1
ON f.fid = c1.fid) tb1
JOIN
(SELECT DISTINCT room AS room2 FROM class) tb2
ON tb1.room = tb2.room2
)
GROUP BY FID
HAVING COUNT(*) IN (SELECT COUNT(DISTINCT Room) FROM Class);