14

I have three tables:

users:        sports:           user_sports:

id | name     id | name         id_user | id_sport | pref
---+--------  ---+------------  --------+----------+------
 1 | Peter     1 | Tennis             1 |        1 |    0
 2 | Alice     2 | Football           1 |        2 |    1
 3 | Bob       3 | Basketball         2 |        3 |    0
                                      3 |        1 |    2
                                      3 |        3 |    1
                                      3 |        2 |    0

The table user_sports links users and sports with an order of preference (pref).

I need to make a query that returns this:

id | name  | sport_ids | sport_names
---+-------+-----------+----------------------------
 1 | Peter | 1,2       | Tennis,Football
 2 | Alice | 3         | Basketball
 3 | Bob   | 2,3,1     | Football,Basketball,Tennis

I have tried with JOIN and GROUP_CONCAT but I get weird results.
Do I need to do a nested query?
Any ideas?

Peter
  • 5,138
  • 5
  • 29
  • 38
  • 2
    could you post your group_concat routine that didn't work? It seems to be exactly what you need (see this page: http://stackoverflow.com/questions/276927/can-i-concatenate-multiple-mysql-rows-into-one-field), so it might be helpful to see the queryt o determine how it went wrong. – user158017 Jun 08 '12 at 21:36

2 Answers2

34

Its not particularly difficult.

  1. Join the three tables using the JOIN clause.
  2. Use Group_concat on the fields you're interested in.
  3. Don't forget the GROUP BY clause on the fields you're not concatenating or weird things will happen


SELECT u.id, 
       u.Name, 
       Group_concat(us.id_sport order by pref) sport_ids, 
       Group_concat(s.name order by pref)      sport_names 
FROM   users u 
       LEFT JOIN User_Sports us 
               ON u.id = us.id_user 
       LEFT  JOIN sports s 
               ON US.id_sport = s.id 
GROUP  BY u.id, 
          u.Name

DEMO

Update LEFT JOIN for when the user doesn't have entries in User_Sports as per comments

Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
  • Amazing! +1 for the sqlfiddle – Peter Jun 08 '12 at 21:48
  • 1
    Why an inner join instead a left join? and a single `group by` seems to be enough: http://sqlfiddle.com/#!2/4d402/9 – Peter Jun 08 '12 at 21:50
  • Inner joins usually perform better than left joins so I start there. Are you interested in Users that don't exist in sports_users? – Conrad Frix Jun 08 '12 at 21:52
  • yes, if an user has no sports, I need a null/blank result for him – Peter Jun 08 '12 at 21:53
  • Ok I've updated the answer and the sqlfiddle for the LEFT JOIN bit. As for the `group by` it's because that's what I'm used to. I don't really have a better reason. In any other DB that wouldn't be legal. Here's the [docs on it](http://dev.mysql.com/doc/refman/5.6/en/group-by-hidden-columns.html) – Conrad Frix Jun 08 '12 at 22:02
  • THANK YOU! I was missing the group by in mine, and getting weird results. :) – James Jun 30 '16 at 01:45
4

I think this is just a simple join and aggregation:

select u.id, u.name, group_concat(s.name order by pref separator ',')
from user_sports us join
     users u
     on us.id_user = u.id join
     sports s
     on us.id_sport = s.id
group by u.id, u.name
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786