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
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