Sayed Rizwan Hashmi Sayed Rizwan Hashmi - 1 year ago 88
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 Source


SELECT Test1.tName, COALESCE(Test2.tMidleName , '') as tMidleName 
from Test1
  ON Test1.idTest1=Test2.idTest1 
WHERE  Test1.tName='nadeem';
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download