Sql Query - Selecting rows where user can be both friend and user
- by Gublooo
Hey
Sorry the title is not very clear. This is a follow up to my earlier question where one of the members helped me with a query.
I have a following friends Table
Friend
friend_id - primary key
user_id
user_id_friend
status
The way the table is populated is - when I send a friend request to John - my userID appears in user_id and Johns userID appears in user_id_friend.
Now another scenario is say Mike sends me a friend request - in this case mike's userID will appear in user_id and my userID will appear in user_id_friend
So to find all my friends - I need to run a query to find where my userID appears in both user_id column as well as user_id_friend column
What I am trying to do now is - when I search for user say John - I want all users Johns listed on my site to show up along with the status of whether they are my friend or not and if they are not - then show a "Add Friend" button.
Based on the previous post - I got this query which does part of the job - My example user_id is 1:
SELECT u.user_id, f.status
FROM user u
LEFT OUTER JOIN friend f ON f.user_id = u.user_id and f.user_id_friend = 1
where u.name like '%'
So this only shows users with whom I am friends where they have sent me request ie my userID appears in user_id_friend.
Although I am friends with others (where my userID appears in user_id column) - this query will return that as null
To get those I need another query like this
SELECT u.user_id, f.status
FROM user u
LEFT OUTER JOIN friend f ON f.user_id_friend = u.user_id and f.user_id = 1
where u.name like '%'
So how do I combine these queries to return 1 set of users and what my friendship status with them is. I hope my question is clear
Thanks
