CODEWITHSUNDEEP
sql
user744692
user744692

Reputation: 13

Database friends list structure and query

I have seen similar post but my problem is not solved i have a database table with fields user_id and friends_id

user_id | friends_id

1 | 2

3 | 4

5 | 1

If I write query

select * 
from friends 
where user_id = 1 or friends_id = 1

This return

user_id | friends_id

1 | 2

5 | 1

The problem is when I try to join friends table with profiles table, profiles table containing display_pic now i try to write query like this

SELECT p.display_pic, f.* 
FROM friends f inner join profiles p on p.user_id = f.user_id
WHERE f.user_id = 1 or f.friend_id = 1

This query will only return display pic of user_id what I want is for first record I need display pic of 2 and for second record I need display pic of 5

I am using mysql database

Please help me Thank you.

Upvotes: 1

Views: 1952

Answers (1)

Manoj
Manoj

Reputation: 5612

try this 

SELECT p.display_pic, f.* 
FROM friends f inner join profiles p on p.user_id = f.user_id 
WHERE f.friend_id = 1
    UNION
SELECT p.display_pic, f.* 
FROM friends f inner join profiles p on p.user_id = f.friend_id 
WHERE f.user_id = 1

Upvotes: 4

Related Questions