Mysql two table query
- by Sergio
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 users.
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.