0

I have one table containing a list of users, all I need from this are users.id and users.username

I have a second table that links the users as "friends", all I need from this is friends.one and friends.two

I want to output a result that shows all friends of a user with a certain user id (this will be a variable, but for the sake of the example we'll use user id '1'). User id '1' can appear in either friends.one or friends.two.

I've tried a few different ideas, but I'm not sure I'm any closer. The code below is obviously awful but I think it describes the idea well(ish). Though I'm probably overly complicating something which there is an easier method for,

SELECT users.username, users.id 
FROM users 
INNER JOIN friends 
   ON users.id = friends.friendone 
WHERE friends.friendtwo='1'

UNION

SELECT users.username, users.id 
FROM users 
INNER JOIN friends 
   ON users.id = friends.friendtwo 
WHERE friends.friendone='1'
ORDER BY users.username ASC;
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
teddytash
  • 21
  • 4
  • 1
    Why do you say the union based solution is awful? You might find description in this answer helpful: https://stackoverflow.com/a/13866221/5749464 – J. Schmale Sep 26 '19 at 18:19
  • Because it didn't work :) – teddytash Sep 26 '19 at 18:26
  • I don't see why your UNION query shouldn't work. Maybe it just needs a small fix. And assuming proper indexing, it's also the fastest solution for MySQL. If things go well for your project, one day you will have a couple of thousands users, and a join like in the accepted answer without an ability to use an index will bring you to ask a new question here on SO. And the solution will be your original query. – Paul Spiegel Sep 26 '19 at 19:21
  • Just found the error: It should be `ORDER BY username ASC` - without the table prefix `users.`. You might also want to use `UNION ALL` if duplicates ar not possible. – Paul Spiegel Sep 26 '19 at 19:24
  • Wow thank you Paul, I guess I wasn't as far away from a solution as I thought I was. Nice to have two options to work with now. – teddytash Sep 26 '19 at 20:29

1 Answers1

0

With conditional join:

SELECT u.username, u.id 
FROM friends f INNER JOIN users u  
ON u.id = CASE '1'
  WHEN f.friendone THEN f.friendtwo
  WHEN f.friendtwo THEN f.friendone
END
forpas
  • 160,666
  • 10
  • 38
  • 76