-2

I have three tables:

students

|id_number| name
| 1001 | Brian | 
| 1002 | John |

terms

| id | year | term | label | 
| 10 | 2022 | 3 | Summer | 
| 11 | 2023 | 1 | 1st Sem | 
| 12 | 2023 | 2 | 2nd Sem | 
| 13 | 2023 | 3 | Summer |

history

|id_number | terms_id | section |
| 1002 | 11 | Oak |
| 1002 | 13 | Fig |
| 1002 | 12 | Daisy |
| 1001 | 10 | Daisy |

Now I want to get the most recent section of each student. All these three tables are expected to be populated with more rows in the coming years.

Desired Result:

| id_number | section | 
| 1002 | Fig   |
| 1001 | Daisy |

I tried this query but I think I lack something or am having the wrong logic. Note that year*3+term will help me achieve the terms in the right order, i.e, 1st, 2nd and summer order.

SELECT s.id_number, h.section
FROM students s 
LEFT JOIN history h ON s.idno = h.idno 
LEFT JOIN terms t ON t.id = h.terms_id
LEFT JOIN (SELECT id, year*3 + term AS ext_term FROM terms) ext ON ext.id = t.id 
WHERE 
    t.id = (
        SELECT MAX(ext_term) 
        FROM ext
        WHERE ext.id = t.id
   )
tatskie
  • 405
  • 1
  • 7
  • 16
  • Have you tried anything??? – Eric Aug 14 '23 at 16:46
  • If you just want one row, write the query that joins all of John's rows, then add `ORDER BY terms_id DESC LIMIT 1`. – Barmar Aug 14 '23 at 16:51
  • What is stopping you getting your desired result? Please include your current attempt and explain where it's not working. – Stu Aug 14 '23 at 17:46
  • @Stu I'ved updated my question. – tatskie Aug 15 '23 at 02:40
  • @Barmar terms_id does not necessarily give the most recent. I update my question and I'm pretty sure `year*3+term` will give the right sequence and hence I can get the most recent one. – tatskie Aug 15 '23 at 02:42
  • Is `idno` supposed to be `id_number`? Why make things confusing by changing the column names between the sample tables and query? – Barmar Aug 15 '23 at 14:16

1 Answers1

0

Use ORDER BY year DESC, term DESC LIMIT 1 to get the most recent year and the latest term within that year.

SELECT s.idno, h.section
FROM students s 
LEFT JOIN history h ON s.idno = h.idno 
LEFT JOIN terms t ON t.id = h.terms_id
WHERE s.name = 'John'
ORDER BY t.year DESC, t.term DESC
LIMIT 1

DEMO

Multiplying the year and adding the term is equivalent, but more confusing in my opinion.

Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Oh I'm sorry I did not emphasize I would want to get all students with their respective most recent terms – tatskie Aug 15 '23 at 15:12
  • 1
    You specifically said "of John", and your attempted code has `AND h.idno = 1002`. You denied that you wanted all student's results. – Barmar Aug 15 '23 at 15:14
  • See https://stackoverflow.com/questions/33145885/sql-selecting-from-two-tables-with-inner-join-and-limit?noredirect=1&lq=1 for how to do it for all students. – Barmar Aug 15 '23 at 15:15