Sql Query - Selecting rows where user can be both friend and user

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 开发者_C百科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

You need to join to your friend table twice:

select u.user_id, f1.status, f2.status
from user u left outer join friend f1 on f1.user_id = u.user_id and f1.user_friend_id = 1
            left outer join friend f2 on f2.user_friend_id = u.user_id and f2.user_id = 1
where u.name like '%'

The first status should show people who have you as a friend, the second status should show people who are your friends. Not sure how this is going to help you find who are friends of a friend, though...

The simplest way to combine the two queries is by performing a union:

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 '%'
UNION
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 '%'

will produce the results that you want

This is what I'm currently using a - union of 2 queries - they are written in Zend framework but pretty straight forward to see the sql:

$select1 = $this->select()             
        ->from(array('f'=>'friend'), array('user_id_friend as friends_id'))
                ->where('user_id='.(int)$user_id)              
        ->where('status = 1');

$select2 = $this->select()             
           ->from(array('f'=>'friend'), array('user_id as friends_id'))        
           ->where('user_id_friend='.(int)$user_id)            
           ->where('status = 1');       

$select = $this->getAdapter()->select()->union(array( '('.$select1.')', '('.$select2.')'));

$stmt = $select->query();
return $stmt->fetchAll();

Hope this helps