Join tables while preserving Null values

Question

I have two tables:

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

I want to select all the values ​​from the users table, but I want to display the status of a specific user (say with id = 2), and the rest is NULL. For example: If I have users:

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

And in the networks:

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

I want to do a John Smith search 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 to make a LEFT JOIN and then WHERE, but this did not work because it excluded lines that are related to other users, but not to this user.

I can do this using the UNION instruction, but I was wondering if it is possible to do this without UNION.

Answer 1

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

 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 all users (except John Smith ) and friend status if John Smith is either a friend of this user or that user is a friend of John Smith .

Answer 2

You probably don't need the WHERE clause, and instead put the condition in the "ON" clause that follows your "LEFT JOIN". This should solve your problems. Also, make sure that the main table is on the left side of the left join, otherwise you must use the correct join.

Answer 3

In addition to the (correct) answers above that such conditions should go in the ON clause, if you really want to put them in the WHERE for any reason, just add a condition that may be zero.

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

Answer 4

From what you have described, this should be a case of combining 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 connection will contain lines from users who do not have a corresponding line in networks and adding restrictions and n.friend_id = 1 when returning the status of friends. Finally, you can exclude the row from users for which the query is being executed.