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.