0

I have 2 tables:

users

  • id_user
  • name
  • picture_url

matches

  • id_match
  • date_match
  • id_user_winner
  • id_user_loser

The query should bring me the list of matches where users.id_user=3 has been the winner. The catch is that the query should bring the name and picture_url from the winner and the loser at the same time.

The curreny query is:

SELECT u.name, u.picture_url, m.id_user_winner, m.id_user_loser, m.date_match FROM matches AS m
INNER JOIN users AS u ON u.id_user = m.id_user_winner WHERE u.id_user = 3

But that will only get me the name and picture of the winner. I need the name and picture of the loser as well. Any ideas? Thanks!

Andres SK
  • 10,779
  • 25
  • 90
  • 152

1 Answers1

1
SELECT u.name
     , u.picture_url
     , m.id_user_winner
     , m.id_user_loser
     , e.name
     , e.picture_url
     , m.date_match 
FROM matches m JOIN users u ON u.id_user = m.id_user_winner AND u.id_user = 3
               JOIN users e ON e.id_user = m.id_user_loser
potashin
  • 44,205
  • 11
  • 83
  • 107
  • Worked out perfectly. Just one quick question: what is the benefit on adding the u.id_user = 3 in the JOIN instead of putting it in the WERE clause? – Andres SK May 16 '14 at 23:47
  • 1
    @andufo : It doesn't really matter for `INNER JOIN` (explicit and implicit `JOIN` as a combination of two here), but for other types you can read about it [here](http://stackoverflow.com/a/354094/3444240) – potashin May 16 '14 at 23:54