SQL Except error?

[Aedahn]

I am trying to find the logical error behind an example of a database in one of my textbooks, and I am completely stumped on what the issue may be. I've tried researching it and everything. Maybe if someone else could see the problem, I'd appreciate the help.

Find the last names of the employees who do not work in the research department.

(SELECT LNAME FROM EMPLOYEE )
EXCEPT
(SELECT LNAME FROM EMPLOYEE, DEPARTMENT
WHERE EMPLOYEE,DNO = DEPARTMENT.DNUMBER AND DNAME LIKE 'Research');

The site in which the information is from is

http://pisang.computing.dcu.ie:9000/CA218/secure/lab/chapter6/sql/6Tables.html

 

[PHV]

Why not simply this ?
SELECT E.LNAME
FROM EMPLOYEE E LEFT JOIN DEPARTMENT D ON E.DNO = D.DNUMBER
WHERE D.DNAME = 'Research' OR D.DNAME IS NULL

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PHV]

OOps, sorry for the typo:
SELECT E.LNAME
FROM EMPLOYEE E LEFT JOIN DEPARTMENT D ON E.DNO = D.DNUMBER
WHERE D.DNAME <> 'Research' OR D.DNAME IS NULL

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Aedahn]

That is quite helpful thank you, and that will work fine. I appreciate the help. The only other thing I would ask is whether or not you would know why it is that the previous coding won't work, or what I would be able to do including the EXCEPT. Unfortunately I have to keep the the EXCEPT included in the coding =/

 

[PHV]

why it is that the previous coding won't work
Because JetSQL don't recognize the EXCEPT instruction.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Aedahn]

Alright, I appreciate the help you've given, thanks a lot.

 

[PHV]

If you don't like JOIN you may use IN:
SELECT LNAME
FROM EMPLOYEE
WHERE DNO NOT IN(SELECT D.DNUMBER FROM DEPARTMENT WHERE D.DNAME='Research')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886