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-tp21676221p21687480.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
