Going wrong in SQL join

Question

I've a table called tbl_users

================================================================================
system_name | user_id | username | password | system_id | group_id | user_level
================================================================================
Demo        | 12345   | demo     | demo     | 99999     | 54321    | Admin
================================================================================
Demo        | 78794   | demo2    | demo2    | 99999     |          | Employee
================================================================================

And second table as tbl_groups

=================================
system_id | group_id | group_name
=================================
99999     | 54321    | engine     
=================================

Am using this query :

SELECT username, user_level, system_name, tbl_users.system_id, tbl_users.user_id, tbl_users.group_id, group_name 
FROM tbl_users JOIN tbl_groups 
WHERE tbl_users.group_id = tbl_groups.group_id 
AND system_name = 'demo' 
AND tbl_users.system_id = '99999'

But using the above query am just getting a row of Demo User, I want Demo 2 also but can't get it done, If am removing tbl_users.group_id = tbl_groups.group_id I get both rows but in group_name it's printed engine in Demo2 User group_name column which is wrong as he has no group currently, so how can I query that I get all users and names of groups if a user is in or else I want group_name column blank if user is in no group.

Answer 1

If you want it return all rows in the left table, use LEFT JOIN instead of JOIN. Also, you should use ON instead of WHERE when specifying JOIN criteria.

SELECT username, user_level, system_name, tbl_users.system_id, tbl_users.user_id, tbl_users.group_id, group_name
FROM tbl_users LEFT JOIN tbl_groups
ON tbl_users.group_id = tbl_groups.group_id

Answer 2

You are looking for an OUTER JOIN:

FROM tbl_users 
LEFT OUTER JOIN tbl_groups 
  ON tbl_users.group_id = tbl_groups.group_id
WHERE ...