Sql Query - stuck with simple left join

Question

I've been stuck with this simple Sql query for last 3 hours and my mind is drawing blank.

I have a User table that captures name user_id etc Then I have a friends table that captures who is friend of whom and their status

User

user_id
name

Friend

friend_id
user_id
user_friend_id
status (accepeted,pending etc)

What I want to do is - when a logged in user searches for a name say "John" - display a list of all johns on the website and next to each John - do a lookup with a friends table to see if the record exists - ie if the logged in User is a friend of any of the Johns

• if it exists show the current status
• if it does not exist then show "Add Friend"

So should I first run a query on User table like select user_id from User where name Like 'John%' and then use those Id's and run them against Friends table.

I am not able to get it. Thanks

Answer 1

Following query must solve your problem

select a.user_name,case when a.user_name like 'John%' then b.status else 'Add Friend' end case from users_det a,friend b where a.user_id = b.user_id

Answer 2

You start with all users that match your criterium (I'm not sure about the exact MySQL syntax):

SELECT u.user_id
FROM User u
WHERE u.name LIKE '%John%'

This gives you all target users. Next you want an outer join on the Friend table where you restrict the results to the currently logged in user:

SELECT u.user_id, f.status
FROM user u
LEFT OUTER JOIN Friend f ON f.friend_id = u.user_id
WHERE f.user_id = <currentUserId> AND u.name LIKE '%John%'

What you get is a result set that has the user ids of all users named John and a status flag that is either null or an actual status. If it is null, you have a potential new friend, if not, you have an existing friend.

Answer 3

Something like this might get you started.

• COALESCE

SQL Statement

SELECT u.name, COALESCE(f.Status, 'Add Friend')
FROM   User u 
       LEFT OUTER JOIN Friend f ON f.User_ID = u.User_ID
WHERE  u.name = 'John'