Northfield82 Northfield82 - 14 days ago 4
MySQL Question

Is it possible to add a JOIN to this SQL statement?

I have a table and a query that I use to return a list of players who have scored the most goals.

The query works great, however in order for me to get the users' name I will have to perform another query based on the returned results (player_id).

Is it possible to amend my existing query as to join the two tables? I know this is possible with a normal query, I'm just unsure whether it is in this instance because of the custom results table being returned.

This is my initial table, called

results
:

+----------------+-------------+-----+
| Field | Type | Key |
+----------------+-------------+-----+
| results_id | int | Pri |
| community_id | int | |
| player1_id | int | |
| player1_goals | int | |
| player2_id | int | |
| player2_goals | int | |
+----------------+-------------+-----+


This is the query that I am using to return my results:

select player, sum(goals) from
((select player1_id as player, player1_goals as goals from results where community_id = 5 )
union all
(select player2_id as player, player2_goals as goals from results where community_id = 5 )
) p
group by player
order by sum(goals) desc


And this is how my results are being returned:

+----------------+------------+
| Player | sum(goals) |
+----------------+------------+
| 2 | 94 |
| 14 | 63 |
| 7 | 43 |
+----------------+------------+


Is it possible to amend the above query and add a
join
to my
users
table:

+--------+-----------+
| id | user_name |
+---------------------+
| 2 | John |
| 7 | Andrew |
| 14 | Charles |
+--------+------------+


To get an output of:

+----------------+----------------+------------+
|user_name | Player | sum(goals) |
+----------------+----------------+------------+
| John | 2 | 94 |
| Charles | 14 | 63 |
| Andrew | 7 | 43 |
+----------------+----------------+------------+

Answer

Short answer - yes, you can:

select u.user_name, sum(goals) from 
((select player1_id as player, player1_goals as goals from results where community_id = 5 ) 
union all 
(select player2_id as player, player2_goals as goals from results where community_id = 5 ) 
) p 
join users u on p.player = u.id
group by player 
order by sum(goals) desc