CODEWITHSUNDEEP
mysql
Adham
Adham

Reputation: 64854

How to retrieve records from First table that don't have a reference in the Second table?

I have a database that contains a two tables, where these table have a relation between them, i want to retrieve records from first table that they don't have a reference in the second table ( the id's that appear in the first only) ..

what is the sql command that do that ?

Upvotes: 0

Views: 113

Answers (4)

Raj More
Raj More

Reputation: 48014

SELECT FirstTable.* 
FROM FirstTable
LEFT JOIN SecondTable
ON FirstTable.FirstTableId = SecondTable.FirstTableId
WHERE 1=1
AND SecondTable.SecondTableId IS NULL

Upvotes: 0

Galz
Galz

Reputation: 6832

Using JOIN:

SELECT * FROM
table_a
LEFT JOIN table_b ON table_a.id = table_b.a_id
HAVING table_b.some_column = NULL;

Using NOT IN:

SELECT * FROM
table_a 
WHERE table_a.id NOT IN (SELECT table_b.a_id from table_b);

If you are dealing with big tables, the left join will probably be much quicker.

Upvotes: 0

Peter Perháč
Peter Perháč

Reputation: 20782

Use the NOT IN to specify that you're only interested in rows whose id has not been referenced from the other table

select * from table1 where table1.id NOT IN (select distinct referenced_id from table2)

Upvotes: 1

Tyler Ferraro
Tyler Ferraro

Reputation: 3772

Select * From Table1
Where Table1.ReferenceID NOT IN (SELECT id From Table2)

Upvotes: 2

Related Questions