MySQL query for two way relationship

Question

For a social web application I needed to create a MySQL table in following way,

id | user_id | friend_id
------------------------
 0 |    5    |   6
 1 |    6    |   5

user 5 and another user 6 are now friends. How can I get a list of friends for a particular user. Could somebody point me in to the right direction please?

`SELECT` the friend id `WHERE` the user id equals to something ? — iamsleepy, Apr 06 '14 at 16:09
@shan @iamsleepy means `SELECT friend_id FROM table_name WHERE user_id = 5` — Rahil Wazir, Apr 06 '14 at 16:12
@Mihai: thats all I got, Thats the problem I'm facing. I wrote other codes, except code to get friends list. So I cannot change the table structure now. — Dan Jay, Apr 06 '14 at 16:23

score 1 · Answer 1 · answered Apr 06 '14 at 16:11

1

If you store friend_ids for all users, you can try this:

SELECT friend_id FROM TableName WHERE user_id=@userID

Pass the parameter @userID from the program.

Example:

SELECT friend_id FROM TableName WHERE user_id=5

EDIT

For both fields:

SELECT CASE WHEN friend_id=5 THEN user_id 
            WHEN user_id=5 THEN friend_id 
            END AS Friend
 FROM TableName

answered Apr 06 '14 at 16:11

Raging Bull

18,593
13
50
55

The both rows is a must to make a comeplete friendship. There may be one. But I need the both rows are met by the query. – Dan Jay Apr 06 '14 at 16:13
yeah!!! I was looking for something like that. Thanks for enlightening me @Raging Bull – Dan Jay Apr 06 '14 at 16:19

score 1 · Accepted Answer · edited May 23 '17 at 12:30

1

SELECT f1.user_id, f2.user_id as 'Friend'
    FROM friends f1 left join friends f2
   on f1.user_id = f2.friend_id

For more information please refer to this post What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

edited May 23 '17 at 12:30

Community

1
1

answered Apr 06 '14 at 18:00

Ananth C

20
4

Thank you for the answer. How can I get this list for a particular userid? – Dan Jay Apr 07 '14 at 04:23
1

Add a where condition to the same query - 'where f1.user_id = 5' – Ananth C Apr 07 '14 at 07:04

score 0 · Answer 3 · edited Apr 06 '14 at 16:35

0

@userid should have the same datatype as in table or you need to convert it to the same

select friend_id where user_id=@userid

edited Apr 06 '14 at 16:35

StarsSky

6,721
6
38
63

answered Apr 06 '14 at 16:19

Stephon Johns

341
2
4

MySQL query for two way relationship

3 Answers3