Cnichols <cnichol...@gmail.com> wrote:
> The following produces 52 results, which consists of 52 questions
> that where incorrect from session 6, the previous session, we are now
> in a new session, session 7
>
> SELECT COUNT() FROM Questions Q
> LEFT JOIN Stats S ON S.QuestionId = Q.Id
> WHERE S.SessionId = 6
> AND S.Correct = 0
Note that LEFT JOIN doesn't do anything here that INNER JOIN wouldn't
do. Your WHERE clause filters out all records that have NULLs in Stats
fields, even if LEFT JOIN produced any.
> Now while in session 7 46 of those questions have been asked but a
> question may have been asked twice (which is fine)
>
> The question is ....
> How do I filter the result of 52 to just select only questions that
> have not been asked (questions that have not been recorded in stats
> with a session of 7)?
select * from Stats S join Questions Q on (S.QuestionId = Q.Id)
where S.SessionId = 6 and S.Correct = 0 and S.QuestionId not in (
select S2.QuestionId from Stats S2 where S2.SessionId = 7);
Igor Tandetnik
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
