I am trying to keep my thoughts as organized as possible. I want to refresh and try to simplify my question ...
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 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)? Cnichols wrote: > > Ok I also tried to think about it from a different approached and didnt > get the result I wanted ... > > SELECT COUNT() FROM Temp > WHERE Id IN (SELECT QuestionId FROM Stats WHERE SessionId = 7) > > I expected that to return 46 results .. it returned 29? ... also kind of > confusing > What I was thinking that happening there is that We are looping through > each row in Temp and seeing if that Id is present in Stats table with a > session of 7 > since Stats has 46 results with 7 and ALL those QuestionIds assoc with 7 > are in the temp (id) table I don not understand why I would also get only > 29 > > > Cnichols wrote: >> >> I expected it to return 46 results, not fewer as you mentioned, except it >> returns 51 which I am trying to figure out why ... there is only one >> duplicate in Temp, but why would that still produce more results then 46? >> >> Igor Tandetnik wrote: >>> >>> "Cnichols" <cnichol...@gmail.com> wrote >>> in message news:21685825.p...@talk.nabble.com >>>> Stats does contain 46 qids with sids of 7 >>>> there is one duplicate question now that i look harder in temp which >>>> is ok because 289 I forgot to mention that id in temp is not a key, >>>> this is because there is a test mode to ask random questions that >>>> aren't unique (so 289 was asked twice). >>>> >>>> but if i did this >>>> SELECT Count() FROM Stats S >>>> LEFT JOIN Temp T ON T.Id = S.QuestionId >>>> WHERE S.SessionId = 7 >>>> >>>> Shouldn't only still return 46 rows and not 51 since stats in on the >>>> left and we are only getting associated data from the right? >>> >>> LEFT JOIN produces all the same rows as INNER JOIN, plus some rows from >>> left table that don't have a match in the right one. Thus, LEFT JOIN >>> cannot possibly generate fewer rows than a corresponding INNER JOIN. I'm >>> not sure why you expect it to. >>> >>> Igor Tandetnik >>> >>> >>> >>> _______________________________________________ >>> sqlite-users mailing list >>> sqlite-users@sqlite.org >>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>> >>> >> >> > > -- View this message in context: http://www.nabble.com/INNER-JOIN%2C-JOIN-Confusin-tp21676221p21687985.html Sent from the SQLite mailing list archive at Nabble.com. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
