Saturday 31 August 2013

select everything from a table for a column

select everything from a table for a column

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

No comments:

Post a Comment