Board index » delphi » EXCEPT clause

EXCEPT clause


2005-09-28 12:18:06 AM
delphi69
Hi there,
Please would you help me with my SQL.
I need to SELECT records from TABLE A that are not already in
TABLE B.
SELECT TableA.EmployeeNumber, TableA.CostCode
FROM TableA
EXCEPT
SELECT TableB.EmployeeNumber, TableB.CostCode
FROM TableB
I am getting the following error message: Invalid syntax near the
keyword ‘EXCEPT?
TABLE A and TABLE B have the same key structure.
I am unable to use the NOT IN clause as I need to specify as many
fields as is defined in the key structure.
I have also been unsuccessful in my attempt in extracting records
based on the keys not being equal.
I would appreciate your thoughts.
Thank you,
Heidi.
 
 

Re:EXCEPT clause

Heidi,
Without knowing the target RDBMS, this might work (if your using MS SQL)..
Select
A.EmployeeNumber,
A.CostCode
From
dbo.TableA A
LEFT OUTER JOIN dbo.TableB B ON B.EmployeeNumber = A.EmployeeNumber AND
B.CostCode = A.CostCode
Where
B.EmployeeNumber IS NULL
AND
B.CostCode IS NULL
Good luck,
krf
"Heidi" <XXXX@XXXXX.COM>writes
Quote

Hi there,

Please would you help me with my SQL.

I need to SELECT records from TABLE A that are not already in
TABLE B.

SELECT TableA.EmployeeNumber, TableA.CostCode
FROM TableA
EXCEPT
SELECT TableB.EmployeeNumber, TableB.CostCode
FROM TableB

I am getting the following error message: Invalid syntax near the
keyword 'EXCEPT'

TABLE A and TABLE B have the same key structure.

I am unable to use the NOT IN clause as I need to specify as many
fields as is defined in the key structure.

I have also been unsuccessful in my attempt in extracting records
based on the keys not being equal.

I would appreciate your thoughts.

Thank you,
Heidi.


 

Re:EXCEPT clause

Hi Kevin,
Your suggestion works perfectly! Thank you so much.
Heidi.
 

Re:EXCEPT clause

Hi,
Quote
I need to SELECT records from TABLE A that are not already in
TABLE B.
SELECT
TableA.EmployeeNumber,
TableA.CostCode
FROM
TableA
WHERE
NOT EXISTS(SELECT 1 FROM TableB
WHERE TableB.EmployeeNumber = TableA.EmployeeNumber and
TableB.CostCode = TableA.CostCode)
Regards,
Arno Brinkman
ABVisie
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
General database developer support:
www.databasedevelopmentforum.com
Firebird open source database (based on IB-OE) with many SQL-99 features:
www.firebirdsql.org
www.firebirdsql.info
Support list for Interbase and Firebird users:
XXXX@XXXXX.COM
Nederlandse firebird nieuwsgroep: