Skip to content
Advertisement

SQL query returning 0 rows after adding extra INNER JOIN

The issue is simple to explain, but seemingly not so simple to solve.

This SQL query returns 0 rows:

SELECT app_handover.*, sys_logins.first_name, sys_logins.last_name, sys_logins.dept_id, org_depts.dept_name
FROM ((app_handover
INNER JOIN sys_logins ON app_handover.sent_by = sys_logins.id)
INNER JOIN org_depts ON sys_logins.dept_id = org_depts.id)
WHERE app_handover.id = '12'

This one, however, returns 1 row as I expected:

SELECT app_handover.*, sys_logins.first_name, sys_logins.last_name, sys_logins.dept_id
FROM app_handover
INNER JOIN sys_logins ON app_handover.sent_by = sys_logins.id
WHERE app_handover.id = '12'

The only difference is the lack of INNER JOIN with the org_depts table.

What I want to be returned is all the column values from app_handover, which references sys_logins.id in its sent_by column. I also want the value of org_depts.dept_name, which is referenced by sys_logins.dept_id.

I cannot think of an alternative syntax to use, but clearly there is an error in my logic in the first example, which is returning 0 rows instead of 1 as I expected.

Advertisement

Answer

Yep, LEFT JOIN was the answer. The value of dept_id in the matching sys_logins row was NULL, so no matching record was found in org_depts.

This query works and now returns 1 row:

SELECT app_handover.*, sys_logins.first_name, sys_logins.last_name, sys_logins.dept_id, org_depts.dept_name
FROM ((app_handover
INNER JOIN sys_logins ON app_handover.sent_by = sys_logins.id)
LEFT JOIN org_depts ON sys_logins.dept_id = org_depts.id)
WHERE app_handover.id = '12'

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement