CODEWITHSUNDEEP
mysql
Daniel
Daniel

Reputation: 1253

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?

Upvotes: 1

Views: 51

Answers (1)

Serge
Serge

Reputation: 4036

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
;

Upvotes: 1

Related Questions