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

Question

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

Just use `LEFT JOIN`. `SELECT T1.*, T2.* FROM tbl_users T1 LEFT JOIN tbl_moreInfo T2 ON T1.ID=T2.ID WHERE T1.ID=1` — 1000111, Aug 21 '16 at 05:29
is that it? wow it work! whoever you are, you're my hero ~ Thanks! — Jeremy, Aug 21 '16 at 05:32
See also [What is the difference between "INNER JOIN" and "OUTER JOIN?"](http://stackoverflow.com/q/38549/1695906) — Michael - sqlbot, Aug 21 '16 at 15:12

score 0 · Accepted Answer · answered Aug 21 '16 at 05:36

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

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

1 Answers1