Jason
Jason

Reputation: 1137

MySQL Select from Multiple Tables and most recent record

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

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562348

This is a variation of the 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

Lukasz Szozda
Lukasz Szozda

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

Related Questions