Mysql two table query

I'm using two tables. First (friendlist), which contains users who are on the list of friends and the other table (members) that contains the basic data of the use开发者_如何学Gors.

Friendlist looks like:

     id    |  myid  |    date     | user
 -----------------------------------------
    001    | 50624  |  2010-01-01 | 32009 
    002    | 41009  |  2010-05-05 | 50624
    003    | 50624  |  2010-03-02 | 23007

The column "myid" contains members who added other users (those from column "user") to their frindlist. I want to get the list of all users that I have added to list and those who add me to their friendlist.

In this example, if my id is 50624, the list would look like:

| allfriends  |
---------------
    32009
    41009
    23007

Then I need to check all users from "allfriend" list with data from the table "members". I want to get only the users with status 1.

The members table looks like:

   id   |   status   |   photo    
--------------------------------
  32009 |     0      |   1.jpg
  41009 |     1      |   2.jpg
  23007 |     1      |   3.jpg      

How this mysql query should look like? Thanks for any help.

---

SELECT id, status, photo FROM members WHERE id IN(
    SELECT user FROM friendlist WHERE myid = 50624
    UNION ALL
    SELECT myid FROM friendlist WHERE user = 50624
) AND status = 1

---

SELECT user AS allfriends
FROM friendlist
INNER JOIN members
ON user = id
WHERE myid = 50624 AND status = 1
UNION
SELECT myid AS allfriends
FROM friendlist
INNER JOIN members
ON user = id
WHERE user = 50624 AND status = 1`

---

my friendlist:

select 
  members.*
from friendlist 
inner join members
  on members.id=friendlist.user
where friendlist.myid=50624 and members.status=1;

people who are friend with me

select 
  members.*
from friendlist 
inner join members
  on members.id=friendlist.myid
where friendlist.user=50624 and members.status=1;

To combine both results, use union

select 
  members.*
from friendlist 
inner join members
  on members.id=friendlist.user
where friendlist.myid=50624 and members.status=1
union
select 
  members.*
from friendlist 
inner join members
  on members.id=friendlist.myid
where friendlist.user=50624 and members.status=1;

---

Something like this should work:

SELECT ALLFRIENDS.id, MEMBERS.status FROM members MEMBERS
JOIN (SELECT id FROM (
                      SELECT myid AS id FROM friendlist WHERE user=50624
                      UNION
                      SELECT user AS id FROM friendlist WHERE myid=50624
                     ) AS tmp
      ) AS ALLFRIENDS ON ALLFRIENDS.id = MEMBERS.id
WHERE MEMBERS.status = 1;