CODEWITHSUNDEEP
sql
I kiet
I kiet

Reputation: 174

select from table a where record is not in table b and table c

select from table a where record is not in table b and table c

i have tried somtihing like this

    select a.* from table a
left outer join b on b.index=a.index
left outer join c on c.index=a.index

Thanks

Upvotes: 0

Views: 1863

Answers (5)

Th3Nic3Guy
Th3Nic3Guy

Reputation: 1979

try

select * 
from table_a a 
where a.index not in (select b.index from table_b b) 
and a.index not in (select c.index from table_c c)

Upvotes: 0

Simon
Simon

Reputation: 1605

Try using an exception join instead :

SELECT A.* FROM TABLE A
    LEFT EXCEPTION JOIN B ON B.INDEX=A.INDEX
    LEFT EXCEPTION JOIN C ON C.INDEX=A.INDEX

Upvotes: 0

slashingweapon
slashingweapon

Reputation: 11317

I'm assuming that your query gives you all records in a. All you need to do now is add a where clause:

select a.* from table a
left outer join b on b.index=a.index
left outer join c on c.index=a.index
where b.index is null and c.index is null

Upvotes: 0

Matt Busche
Matt Busche

Reputation: 14333

Where b.index and c.index are columns in the respective tables.

select a.* from table a
    left outer join b on b.index=a.index
    left outer join c on c.index=a.index
WHERE b.index IS NULL
AND c.index IS NULL

Upvotes: 1

Stephan
Stephan

Reputation: 1246

try

SELECT a.* 
FROM a 
WHERE a.`index` NOT IN (SELECT `index` FROM b) 
AND a.`index` NOT IN (SELECT `index` FROM c);

Upvotes: 0

Related Questions