Jeremy Jeremy - 3 months ago 14
MySQL Question

MySQL inner join retrieve table 1 even though table 2 does not matched ID

I have this query that basically retrieve 2 tables (tbl_users and tbl_moreInfo) based on selected user ID. It works fine but there are times that the tbl_users has the ID that the tbl_moreInfo doesn't have so it returns empty row. I would still want to retrieve tbl_users even though there are no ID matched in tbl_moreInfo in just 1 query.

This is my query:

SELECT T1.*, T2.*
FROM tbl_users T1
INNER JOIN tbl_moreInfo T2
ON T1.ID=T2.ID
WHERE T1.ID=1


Thanks in Advance :)

Answer

First, You should use in table 2 a reference id of table 1

JOIN tbl_moreInfo T2 ON T1.ID = T2.T1_ID -- NOT T2.ID

And then, if id doesn't exist in table 2, use left join, so filled with null in the data that do not exist.

SELECT T1.*, T2.*
FROM tbl_users T1
LEFT JOIN tbl_moreInfo T2 ON T1.ID=T2.ID
WHERE T1.ID=1