Reputation: 1137
I'm having issues with a select query and can't quite figure out how to fix. I have two tables:
TABLE_students
|--------|------------|--------|
| STU_ID | EMAIL | NAME |
|--------|------------|--------|
| 1 | [email protected] | Bob |
| 2 | [email protected] | Joe |
| 3 | [email protected] | Tim |
--------------------------------
TABLE_scores
|--------|------------|-------------|--------|
| SRE_ID | STU_ID | DATE | SCORE |
|--------|------------|-------------|--------|
| 91 | 2 | 2018-04-03 | 78 |
| 92 | 2 | 2018-04-06 | 89 |
| 93 | 3 | 2018-04-03 | 67 |
| 94 | 3 | 2018-04-06 | 72 |
| 95 | 3 | 2018-04-07 | 81 |
----------------------------------------------
I'm trying to select data from both tables but have a few requirements. I need to select the student even if they don't have a score in the scores table. I also only only want the latest scores record.
The query below only returns those students that have a score and it also duplicates returns a total of 5 rows (since there are five scores). What I want is for the query to return three rows (one for each student) and their latest score value (or NULL if they don't have a score):
SELECT students.NAME, scores.SCORE FROM TABLE_students as students, TABLE_scores AS scores WHERE students.STU_ID = scores.STU_ID;
I'm having difficulty figuring out how to pull all students regardless of whether they have a score and how to pull only the latest score if they do have one.
Thank you!
Upvotes: 1
Views: 1601
Reputation: 562348
This is a variation of the greatest-n-per-group question, which is common on Stack Overflow.
I would do this with a couple of joins:
SELECT s.NAME, c1.DATE, c1.SCORE
FROM students AS s
LEFT JOIN scores AS c1 ON c1.STU_ID = s.STU_ID
LEFT JOIN scores AS c2 ON c2.STU_ID = s.STU_ID
AND (c2.DATE > c1.DATE OR c2.DATE = c1.DATE AND c2.SRE_ID > c1.SRE_ID)
WHERE c2.STU_ID IS NULL;
If c2.STU_ID is null, it means the LEFT JOIN matched no rows that have a greater date (or greater SRE_ID in case of a tie) than the row in c1. This means the row in c1 must be the most recent, because there is no other row that is more recent.
P.S.: Please learn the JOIN
syntax, and avoid "comma-style" joins. JOIN
has been standard since 1992.
P.P.S.: I removed the superfluous "TABLE_" prefix from your table names. You don't need to use the table name to remind yourself that it's a table! :-)
Upvotes: 1
Reputation: 175716
You could use correlated subquery:
SELECT *,
(SELECT score FROM TABLE_scores sc
WHERE sc.stu_id = s.stu_id ORDER BY DATE DESC LIMIT 1) AS score
FROM TABLE_students s
Upvotes: 0