79153093

Date: 2024-11-03 15:55:23
Score: 1
Natty:
Report link

Ok, now I have the final answer. Thanks to the 1st answer from this question on Stack Exchange.

Apparently Sqlite only disallows access to outer tables when directly joining subqueries. However, it does allow access to outer tables inside subqueries that are part of "on" clauses.

In the example below, I'm using a "correlated scalar subquery" (according to "explain query plan") which allows full access to the current row of the parent student table. There's even no need to use a "limit 1" in the subquery, because Sqlite assumes only the 1st row should be returned in order to satisfy the "on" clause.

select
  student.name,
  testScore.score as topScore,
  testScore.day as topScoreDay
from
  student
  left join testScore on testScore.id = (
    select id from 
      testScore 
    where 
      testScore.studentId = student.id
    order by 
      score desc
  )
order by
  topScore desc;
Reasons:
  • Blacklisted phrase (0.5): Thanks
  • Long answer (-0.5):
  • Has code block (-0.5):
  • Self-answer (0.5):
  • Low reputation (1):
Posted by: Bill F.