CODEWITHSUNDEEP
mysqlsql
smarttechy
smarttechy

Reputation: 902

How to check if every primary key value is being referenced as foreign key in another table

I have two table tb1 and tb2 . id in tb1 is pk and referenced as fk in tb2. I want to know if tb1 has id values as 1,2,3,4,5 and tb2 has fk_values for 1,2,3,4 but doesn't have for pk 5 how can i find this.

tb1
------
id   
------
1    
------
2   
------
3    
------
4
------
while tb2 

fk_id
-------
1
--
1
--
2
--
3
--
3
--

but table 2 doesn't have values for 4 then how can i find out the 4 value here .

Database to be used is mysql.

Upvotes: 2

Views: 1230

Answers (2)

stackoverflow
stackoverflow

Reputation: 78

use the left join .left join will give you the result that only exists in the left table.which means you must use tb1 left join tb2 like this.

// pick the tb1.id in the result when the fk_id is null.
select tb1.id from tb1 left join tb2 on tb1.id = tb2.fk_id where tb2.fk_id is null;

Upvotes: 0

e4c5
e4c5

Reputation: 53744

To find what's in tb1 but not in tb2 do this:

SELECT tb1.* FROM tb1 LEFT JOIN tb2 ON tb1.id = tb2.fk_id WHERE tb2.fk_id IS NULL

To do it the other way (in tb2 but not in tb1 ), which cannot be in this case because, there is a foreign key, but none the less it might be useful for you later simply switch the two tables

SELECT tb2.* FROM tb2 LEFT JOIN tb1 ON tb1.id = tb2.fk_id WHERE tb1.id IS NULL

Upvotes: 3

Related Questions