CODEWITHSUNDEEP
sql-server
Eliseo Jr
Eliseo Jr

Reputation: 141

Display all records in third table except for records not found in either of 2 other tables

I have 3 tales Table A, Table B and Table C with the same table structure. What is the fastest way to display all records in Table C except for records not found in either Table A or Table B

Table A

IDNO
------
A100
A200
A300

Table B

IDNO
------
T555

Table C

IDNO
-------
A100
A200
A300
T555
X999

EXPECTED OUTPUT

RECNO
------
A100
A200
A300
T555

This is my script, but not sure if it is the fastest one.

SELECT a.* 
FROM TABLEC as a LEFT OUTER JOIN 
TABLEA as a on a.[RECNO] = b.[RECNO] LEFT OUTER JOIN
TABLEB as b on a.[RECNO] = c.[RECNO]
WHERE b.[RECNO] IS NOT NULL Or
c.[RECNO] IS NOT NULL

Upvotes: 0

Views: 44

Answers (2)

Squirrel
Squirrel

Reputation: 24763

make sure you have proper index on RECNO

SELECT c.* 
FROM   TABLEC as c
WHERE  NOT
(
    NOT EXISTS (SELECT * FROM TABLEA a WHERE a.RECNO = c.RECNO)
AND NOT EXISTS (SELECT * FROM TABLEB b WHERE b.RECNO = c.RECNO)
)

Upvotes: 1

TomC
TomC

Reputation: 2814

This is easy, I would do this:

select * from Tablec
where not exists(select * from tablea where Tablea.idno=TableC.IDNO)
or not exists (select * from tableb where Tableb.idno=TableC.IDNO)

Just make sure your have the appropriate indexes.

Upvotes: 1

Related Questions