CODEWITHSUNDEEP
mysql
user187580
user187580

Reputation: 2315

How to retrieve all of the records which dont have any reference in another table?

TableA 
id, name
1, abc
2, cde
3, def


TableB
id, TableA_id, topic
1, 1, blah 
2, 1, blah again
3, 2, abcdef

I want to select all of those records from TableA which dont have any references in TableB. How do I do it in Mysql?

Thanks a lot.

Upvotes: 1

Views: 100

Answers (4)

brendan
brendan

Reputation: 29986

Select a.*
From TableA a
Left Outer Join TableB b on b.TableA_id=a.id
Where b.id is NULL

Upvotes: 1

Corey Ballou
Corey Ballou

Reputation: 43477

SELECT TableA.*
FROM TableA
LEFT JOIN TableB ON (TableA.id = TableB.TableA_id)
WHERE TableB.id IS NULL

Upvotes: 1

Tim Ebenezer
Tim Ebenezer

Reputation: 2724

SELECT * FROM TableA WHERE id NOT IN (SELECT TableA_id FROM TableB)

Upvotes: 0

Grzegorz Gierlik
Grzegorz Gierlik

Reputation: 11232

You can use LEFT JOIN and select these records which don't have matching record in B.

SELECT
  TableA.*
FROM
  TableA
  LEFT JOIN
    TableB 
  ON
    TableB.TableA_id = TableA.id
WHERE
  TableB.id IS NULL

Upvotes: 6

Related Questions