Sayed Rizwan Hashmi Sayed Rizwan Hashmi - 4 months ago 7
MySQL Question

how to retrieve value from two table in mysql using foreing Key is null?

i have two tables Test1 and Test2

Test1 have id and Name



Id Name
1 rizwan
2 nadeem
3 ramiz


and second Table Test2 have middle name and foreing key

id MiddleName id_fk
1 Hashmi 1
2 Khan 3


now i want to select value from both table if value middle name is not present in second table then Display only name from first name

i have write a query for this

SELECT Test1.tName,Test2.tMidleName
from Test1,Test2
where Test1.idTest1=Test2.idTest1
AND Test1.tName='nadeem';





Above is query but if foreing key is null it will not show values

what should i change in the query if foreign key is null? if its null i want to show only name from first Table

Answer

Use LEFT JOIN

SELECT Test1.tName, COALESCE(Test2.tMidleName , '') as tMidleName 
from Test1
LEFT JOIN Test2 
  ON Test1.idTest1=Test2.idTest1 
WHERE  Test1.tName='nadeem';
Comments