CODEWITHSUNDEEP
mysqlsqlselectjoinrelational-database
nowiko
nowiko

Reputation: 2557

Select SQL statement on related tables

I have two tables T1 and T2. I want to select all rows from T2 table where t1_id not equal to any id in T1 table. This is not working:

SELECT t2.id, t2.t1_id, t2.data FROM T2.t2, T1.t1 WHERE t2.t1_id != t1.id

Upvotes: 1

Views: 136

Answers (3)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Use LEFT JOIN to fetch all records from left table and add where condition with checking of null value of second table to find unmatched records of left table

Try this: 

SELECT t2.id, t2.t1_id, t2.data 
FROM T2.t2
LEFT OUTER JOIN T1.t1 ON t2.t1_id = t1.id
WHERE t1.id IS NULL

Upvotes: 1

SMA
SMA

Reputation: 37023

You could use subquery with NOT IN clause as below:

SELECT t2.id, t2.t1_id, t2.data 
FROM T2 t2
WHERE t2.t1_id NOT IN (SELECT DISTINCT id
                       FROM T1)

Upvotes: 1

juergen d
juergen d

Reputation: 204766

SELECT t2.id, t2.t1_id, t2.data 
FROM T2.t2
LEFT JOIN T1.t1 ON t2.t1_id = t1.id
GROUP BY t2.id, t2.t1_id, t2.data
HAVING sum(t1.id is not null) = 0

Upvotes: 0

Related Questions