CODEWITHSUNDEEP
sqloracle-database
Hélder Gonçalves
Hélder Gonçalves

Reputation: 3942

Select rows where some values are not in other table

A and B of table T3 are the same as A and B from T1. Basically what I need to do is select all the values that aren't on T3. If there is a line with A,B on T3 I don't wanna show it.

SELECT T1.A, T1.B, T1.C
FROM T1, T2
WHERE T1.X=T2.X
AND NOT EXISTS
(
   SELECT T3.A, T3.B
   FROM T3
)

Any help? Thanks

Upvotes: 0

Views: 183

Answers (4)

schurik
schurik

Reputation: 7928

SELECT T1.A, T1.B, T1.C
FROM T1, T2
WHERE T1.X=T2.X
AND (T1.A, T1.B) NOT IN (SELECT T3.A, T3.B   FROM T3 )

Upvotes: 0

Shahrzad
Shahrzad

Reputation: 1072

You can also do that using subqueries like

SELECT T1.A, T1.B, T1.C
FROM T1, T2
WHERE T1.X=T2.X
AND T1.A NOT IN (SELECT T3.A FROM T3)
AND T2.B Not IN (SELECT T3.B FROM T3)

Upvotes: 0

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

select T1.A,T1.B,T1.C
from T1
inner join T2
on T1.X=T2.X
left join T3 on T1.A=T3.A and T1.B=T3.B
where T3.A is null

Upvotes: 3

Tim Schmelter
Tim Schmelter

Reputation: 460108

SELECT T1.A, T1.B, T1.C
FROM T1 INNER JOIN T2 ON T1.X=T2.X
WHERE NOT EXISTS
(
   SELECT 1 FROM T3
   WHERE T3.A = T1.A AND T3.B = T1.B
)

Upvotes: 4

Related Questions