Select two rows with one sql query

Question

Hi everyone I have this table structure

id | u_id2 | u_id2
1  |   8   |  10
2  |   10  |  1
3  |   9   |  8
4  |   1   |  8

With this sql query it try to get every friend relation with an 8 as user_id

SELECT 
      u.id              
FROM  up2_users u
RIGHT JOIN up2_friends f
ON u.id = f.u_id1
WHERE f.u_id1 = :user_id OR f.u_id2 = :user_id

The result is kind of okay but not totally correct. :user_id = 8

8, 9 , 1

You see in the table above that the 8 is the :user_id and it should return the 10 not the 8 from f.u_id2

But if I type

ON u.id = f.u_id1 AND u.id = f.u_id2

I get all results doubled and the 8s

Is there anyway to accomplishe what I need with in a single query?

The correct return should be 10, 9, 1

Answer 1

You do not need a join for this query. You do want to conditionally return the other user id, so use a case:

SELECT (case when f.u_id1 = :user_id then f.u_id2 else f.u_uid1 end) as id              
FROM  up2_friends f
WHERE f.u_id1 = :user_id OR f.u_id2 = :user_id;

If you don't want duplicates, then use select distinct.

Answer 2

This is working for the username

SELECT (case when f.u_id1 = :user_id then f.u_id2 else f.u_id1 end) as id,
        u.username              
FROM  up2_friends f
RIGHT JOIN up2_users u   
ON  u.id=f.u_id1
WHERE f.u_id1 = :user_id OR f.u_id2 = :user_id
ORDER BY u.username;

Answer 3

@Konter Based on your recent comment on Gordon's suggestion, you might be looking for something like

SELECT 
  u.id
  , Friendship.friend2 friend
FROM  up2_users u
JOIN  (SELECT u_id1 friend1, u_id2 friend2 FROM Friend
      UNION ALL
      SELECT u_id2, u_id1 FROM Friend) Friendship
  ON u.id = Friendship.friend1
WHERE u.id = 8
ORDER BY friend1, friend2
;