I have three tables like this:
person:
id | name | gender --------------------------- 1 Joe male 2 Daniel male 3 Sarah female
person_skills:
person_id | skill_id
---------------------------
1 1
1 2
1 3
2 1
2 2
...
skills:
id | skill_name ----------------------- 1 Writing 2 Programming 3 Singing ...
I need to get all the skills of persons when I just send one of their skills in my query.
This is my code:
SELECT a.name, a.gender,
GROUP_CONCAT(DISTINCT c.skill_name),
GROUP_CONCAT(DISTINCT c.id as skill_id)
FROM persons a
LEFT JOIN person_skills b ON b.person_id = a.id
LEFT JOIN skills c ON c.id = b.skill_id
WHERE b.skill_id = 3 GROUP BY a.id
I want the result:
name | gender | skill_name | skill_id
----------------------------------------------
Joe male Writing 1
Programming 2
Singing 3
But this code return only skill_id "3" and skill_name "Singing".
Thanks in advance.
You'll need to reference some tables twice.
SELECT p.name, p.gender,
GROUP_CONCAT(DISTINCT s.skill_name),
GROUP_CONCAT(DISTINCT s.id as skill_id)
FROM person_skills AS ps0
INNER JOIN persons AS p ON ps0.person_id = p.id
LEFT JOIN person_skills ps ON p.id = ps.person_id
LEFT JOIN skills s ON s.id = ps.skill_id
WHERE ps0.skill_id = 3
GROUP BY p.id
Sidenote: I left it alone, but your grouping criteria can be problematic under certain configurations.
...and alternative, if you averse to the extra table references, but unlikely to be faster is:
SELECT p.name, p.gender,
GROUP_CONCAT(DISTINCT s.skill_name),
GROUP_CONCAT(DISTINCT s.id as skill_id)
FROM persons AS p
LEFT JOIN person_skills ps ON p.id = ps.person_id
LEFT JOIN skills s ON s.id = ps.skill_id
GROUP BY p.id
HAVING COUNT(CASE s.skill_id WHEN 3 THEN 1 ELSE NULL) > 0;