Search code examples
mysqlselectjoingreatest-n-per-group

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!

Solution

  • 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