joining tables while keeping the Null values

Question

I have two tables:

• Users: ID, first_name, last_name
• Networks: user_id, friend_id, status

I want to select all values from the users table but I want to display the status of specific user (say with id=2) while keeping the other ones as NULL. For instance: If I have users:

? first_name  last_name
------------------------
1 John        Smith
2 Tom         Summers
3 Amy         Wilson

And in networks:

user_id   friend_id   status
------------------------------
2         1           friends

I want to do search for John Smith for all other users so I want to get:

id first_name  last_name   status
------------------------------------
2  Tom         Summers     friends  
3  Amy         Wilson      NULL

I tried doing LEFT JOIN and then WHERE statement but it didn't work because it excluded the rows that have relations with other users but not this user.

I can do this using UNION statement but I was wondering if it's at all possible to do it without UNION.

Answer 1

You need to put your condition into the ON clause of the LEFT JOIN.

Select
  u.first_name,
  u.last_name,
  n.status
From users u
Left Join networks n On (    ( n.user_id = 1 And n.friend_id = u.id )
                          Or ( n.friend_id = 1 And n.user_id = u.id )
Where u.id <> 1

This should return you all users (except for John Smith) and status friend if John Smith is either friend of this user, or this user is friend of John Smith.

Answer 2

You probably don't need a WHERE clause, and instead of that, put the condition into the "ON" clause that follows your "LEFT JOIN". That should fix your issues. Also, make sure that the main table is on the left side of the left join, otherwise, you should use a right join.

Answer 3

In addition to the (correct) replies above that such conditions should go in the ON clause, if you really want to put them in the WHERE clause for some reason, just add a condition that the value can be null.

WHERE (networks.friendid = 2 OR networks.friendid IS NULL)

Answer 4

From what you've described, it should be a case of joining a subset of networks to users.

select id, first_name, last_name, status
from users u
left join networks n on u.id = n.user_id
                    and n.friend_id = 1
where id <> 1;                          

The left join will keep rows from users that do not have a matching row in networks and adding the and n.friend_id = 1 limits when the 'friends' status is returned. Lastly, you may choose to exclude the row from users that you are running the query for.