Mysql two table query

Question

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.

Klaus Byskov Pedersen · Accepted Answer · 2010-12-30 13:36:04Z

2

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

answered Dec 30, 2010 at 13:36

Klaus Byskov Pedersen

122k31 gold badges192 silver badges223 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

joelcox · Accepted Answer · 2010-12-30 13:38:04Z

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`

answered Dec 30, 2010 at 13:38

joelcox

5829 silver badges19 bronze badges

Comments

ajreal · Accepted Answer · 2010-12-30 13:47:10Z

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;

edited Dec 30, 2010 at 13:47

answered Dec 30, 2010 at 13:30

ajreal

47.4k11 gold badges97 silver badges119 bronze badges

1 Comment

ajreal Over a year ago

@Sergio - yup, use union, try answer from @Klaus Byskov Hoffmann, looks elegant, I would think you need to keep two list separately?

the_void · Accepted Answer · 2010-12-30 13:38:25Z

0

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;

answered Dec 30, 2010 at 13:38

the_void

5,5602 gold badges31 silver badges35 bronze badges

Collectives™ on Stack Overflow

Mysql two table query

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related