I have a many to many user-sport relation and I'm getting a concatenated string of all sport names a user plays using a subquery. My structure looks like:
user
===============
| id | name |
---------------
| 1 | Hugo |
| 2 | Paco |
| 3 | Luis |
---------------
sport
=================
| id | name |
-----------------
| 1 | tennis |
| 2 | football |
| 3 | handball |
-----------------
user_sport
======================
| user_id | sport_id |
----------------------
| 1 | 3 |
| 1 | 1 |
| 2 | 1 |
----------------------
how do I filter the results with users that play any sport from a list, for example getting all users who play tennis or handball.
I'm trying with this query:
SELECT u.id, u.name,
COALESCE (
(SELECT GROUP_CONCAT(s.name SEPARATOR ', ')
FROM sport AS s
LEFT JOIN user_sport AS us ON us.sport_id = s.id
WHERE us.user_id = u.id),'') AS sports
FROM user u
WHERE us.sport_id IN (1,3)
GROUP BY u.id
ORDER BY g.name
but it is not working because the where clause doesn't know the user_sport table. So, I have to create a new JOIN outside the subquery?
You have to join with user_sport twice. Once to filter it to just the sports in the list, and the other time to get all the sports that the selected users play in.
SELECT u.id, u.name, GROUP_CONCAT(s.name SEPARATOR ', ') AS sports
FROM user AS u
JOIN user_sport AS us_filtered ON u.id = us_filtered.user_id
JOIN user_sport AS us_all ON u.id = us_all.user_id # all
JOIN sport AS s ON s.id = us_all.sport_id
WHERE us_filtered.sport_id IN (1, 3)
GROUP BY u.id
ORDER BY u.name