My sql is very rusty, but here's the use case. I have two tables, one with quiz questions (db.questions) and one with records for each student's performance on each question (db.question_records). The latter table adds a new row when a particular student tries a question for the first time, and then updates that row on each subsequent attempt. It includes a "date" field for the last successful attempt.
What I need to do is select all of the questions (from db.question) *unless* the student has attempted it already (i.e. there is a corresponding row in db.question_records) AND a successful attempt was already made today (db.question_records.last_right == request.now). So far I have done this: #join the two tables, including questions with no record yet in db.question_records questions = db().select(db.questions.ALL, db.question_records.ALL, left=db.question_records.on(db.questions.id == db.question_records.question)) #exclude questions gotten right today by this user questions1 = questions.exclude(lambda row: (row.question_records.name == auth.user_id) and (db.question_records.last_right != request.now)) Problem: I think in the initial left join I'm only getting the first matching row from db.question_records. So I'd like to be able to filter that table first to include only the rows for the current user.
