CODEWITHSUNDEEP
mysqlsqldatabase
Kiran
Kiran

Reputation: 8538

How to get distinct values from a Table not present in multiple table

I am trying to get a unique list of values from a table which is not present in the corresponding columns in other 2 tables( multiple tables)

Here is how my Table looks :

-----------         -----------         -----------
Table1              Table2              Table3
---|-------         ---|-------         ---|-------
id | Value          id | Value          id | Value
---|-------         ---|-------         ---|-------
1  | A              1  | A              1  | A
2  | B              2  | C              2  | D
3  | C              3  | D              3  | E
4  | D              4  | G              4  | F

Now, the unique value of Table1 is "B" ( This value is not present in Table2 and Tabl3).

Similarly unique value of Table2 is "G". Similarly unique value of Table3 is "E, F".

I am using the following query : 

select Value from Table1 where Table1.Value NOT IN (select Value from Table2);

Any idea how to extend into 2 tables ( or more)?

Thanks

Upvotes: 0

Views: 920

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

You may also use left joins here:

SELECT t1.Value
FROM Table1 t1
LEFT JOIN Table2 t2
    ON t1.Value = t2.Value
LEFT JOIN Table3 t3
    ON t1.Value = t3.Value
WHERE
    t2.Value IS NULL AND t2.Value IS NULL;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Use and:

select Value
from Table1
where Table1.Value not in (select Value from Table2) and
      Table1.Value not in (select Value from Table3) ;

I discourage the using NOT IN with subqueries. It doesn't behave as you would expect with NULL values. If any value in the subquery is NULL, then all rows are filtered out.

Instead, use NOT EXISTS:

select t1.Value
from Table1 t1
where not exists (select 1 from table2 t2 where t2.value = t1.value) and
      not exists (select 1 from table3 t3 where t3.value = t1.value);

Upvotes: 1

Related Questions