how to write conditional left join

Question

I have 2 tables named user and userFriend. I want all user from user table, and specific member from userFriend table. Then I want to join both of them...

user
userID   userName
1         aaa
2         bbb
3         ccc
4         ddd
5         eee

userFriend
userFriendID userID friendUserID
1              1      2
2              2      3 
3              1      4
4              4      2

So if my userID = 1, then I want result like

userID   userName userFriendID  friendUserID      
2          bbb        1          2
3          ccc        NULL       NULL
4          ddd        3          4
5          eee        NULL       NULL

so in this way I want conditional for 2nd table, I only want to join 2nd table having userID = 1 with 1st table using left join.

Answer 1

It's an unconventional thing to ask for... but this gives you the results you want.

SELECT u.userID, u.userName, uf.userFriendID, uf.friendUserID
FROM user u
LEFT JOIN userFriend uf ON u.userID = uf.friendUserID AND uf.userID =1
WHERE u.userID !=1