Shaiful Islam Shaiful Islam - 2 years ago 167
MySQL Question

SQL I have two table which join with foreign key . I want to find out missing id from second table

This two table are join with foreign key. I insert some value in info but i need to find out which value are not inserted into info from name table.
I mean find name.id is not equal info.sid

enter image description here

enter image description here

Answer Source

The normative approach is an anti-join pattern.

To find rows in table1 which have an value in the id column which does not have a matching row in table2 (i.e. there are no rows in table2 with fk_id value that match)

 SELECT t1.id
   FROM table1 t1
   LEFT
   JOIN table2 t2
     ON t2.fk_id = t1.id
  WHERE t2.fk_id IS NULL

This query basically says return all rows from table1, along with any matching rows from table2, but exclude rows where we found a matching row in table2. Leaving us with only rows from table1 that didn't have a match in table2.

This is a demonstration of just one of the approaches. There are a couple of other query patterns that will return an equivalent result.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download