Q:

joining tables while keeping the Null values

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.

A:

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.

A:

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.

