Daniel Daniel - 4 years ago 101
MySQL Question

Group by a row and select multiple normalised columns from the same table

I have tried a quick search, but can't find anything that can help (or don't know the correct terminology)

Here's an ERD of my tables:

ERD of my tables

Here's some sample data from relevant tables:

+------------+----------------+-------+
| sport_id | sport_name | type |
+------------+----------------+-------+
| 1 | 100M | track |
| 2 | 200M | track |
| 3 | Javelin | field |
+---+------------+------------+-------+




+------------+----------------+-------------+
| pupil_id | pupil_name | pupil_class |
+------------+----------------+-------------+
| 1 | John Doe | 1A |
| 2 | Lisa Lawson | 1K |
| 3 | Jamie Wilson | 1F |
+---+------------+------------+-------------+




+----------+----------+----------+-------+
| pupil_id | sport_id | attempt | score |
+----------+----------+----------+-------+
| 1 | 3 | 1 | 20 |
| 1 | 3 | 2 | 30 |
| 1 | 3 | 3 | 40 |
| 2 | 3 | 1 | 22 |
| 2 | 3 | 2 | 32 |
| 2 | 3 | 3 | 33 |
| 3 | 3 | 1 | 23 |
| 3 | 3 | 2 | 33 |
| 3 | 3 | 3 | 43 |
+----------+----------+----------+-------+


What I want to do is end up with the
pupil_name
and all of their attempts in one row, for a selected
sport_id
, for example
WHERE sport.sport_id = 3
:

+--------------+--------+--------+--------+
| pupil_name | score1 | score2 | score3 |
+--------------+--------+--------+--------+
| John Doe | 20 | 30 | 40 |
| Lisa Lawson | 22 | 32 | 42 |
| Jamie Wilson | 23 | 33 | 43 |
+---+------------+------------+-----------+


I have the following query, which merely joins the tables, and I tried using a subquery to get the scores, but I cannot pass the
pupil_id
into the subquery (hence returns
NULL
). This also returns the
pupil_name
three times:

SELECT p.pupil_name,
(SELECT score
FROM result
WHERE attempt = 1
AND pupil_id = p.pupil_id) AS score1
FROM pupil p,
sport s,
result r
WHERE p.pupil_id = r.pupil_id
AND s.sport_id = r.sport_id
AND r.sport_id = 3;


Example result:

+--------------+--------+
| pupil_name | score1 |
+--------------+--------+
| John Doe | NULL |
| John Doe | NULL |
| John Doe | NULL |
+---+------------+------+


How should I approach this? Can I use
JOIN
?

Answer Source

Solution 1: 3 INNER JOIN's

Here's one solution using 3 simple INNER JOIN's:

SELECT p.pupil_name
        ,r1.score AS score1
        ,r2.score AS score2
        ,r3.score AS score3
FROM pupil p
    INNER JOIN result r1
        ON p.pupil_id = r1.pupil_id
        AND r1.attempt = 1
    INNER JOIN result r2
        ON p.pupil_id = r2.pupil_id
        AND r2.attempt = 1
    INNER JOIN result r3
        ON p.pupil_id = r3.pupil_id
        AND r3.attempt = 1
    INNER JOIN sport s
        ON r1.sport_id = s.sport_id
        AND r2.sport_id = s.sport_id
        AND r3.sport_id = s.sport_id
WHERE s.sport_id = 3
;

Solution 2: 1 INNER JOIN and GROUP BY

This solution uses only 1 INNER JOIN to the result table, and then aggregates values in a clever way:

SELECT   p.pupil_name
        ,MAX(CASE WHEN r.attempt = 1 THEN r.score END) AS score1
        ,MAX(CASE WHEN r.attempt = 2 THEN r.score END) AS score2
        ,MAX(CASE WHEN r.attempt = 3 THEN r.score END) AS score3
FROM pupil p
    INNER JOIN result r
        ON p.pupil_id = r.pupil_id
    INNER JOIN sport s
        ON r.sport_id = s.sport_id
WHERE s.sport_id = 3
GROUP BY p.pupil_name
;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download