[SOLVED] MYSQL Query Cross Database Join return NULL

MYSQL Query Cross Database Join return NULL

select t1.CardID,t2.Description,t5.BioData 
from db2.tblemployeeinfob t1
left join (db2.tbldepartments t2,db1.tblbiometrics t5)
on 
(t1.Department = t2.DepartmentID and
t1.CardID=t5.CardID
)

Return Result is 1420 | (NULL) | (NULL)

Expected Result is 1420 | DB2_Description_Value | DB1_BioData_value

if i remove cross database join, like remove db1 then query will work fine to join remaining two tables from same database.

if i do cross database join between db1 and db2, even table t2 from same database db2 is returning NULL.

Where is problem with my Query, so i can get value from both databases.

Solution

You shouldn't be using a cross join here. You want two separate left joins:

SELECT t1.CardID, t2.Description, t5.BioData 
FROM db2.tblemployeeinfob AS t1
LEFT JOIN db2.tbldepartments AS t2 ON t1.Department = t2.DepartmentID
LEFT JOIN db1.tblbiometrics AS t5 ON t1.CardID = t5.CardID