Cnichols <cnichol...@gmail.com> wrote: > 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. > > I expected it to return 46 results, not fewer as you mentioned,
What do you mean, not fewer? You had a statement with an INNER JOIN that returned 51 rows, when you expected 46. You attempted to fix the problem by replacing INNER JOIN with LEFT JOIN, and were surprised to discover that the statement still returned 51 rows. I'm trying to point out that your attempt was misguided: a LEFT JOIN cannot possibly ever return fewer rows than a corresponding INNER JOIN. Perhaps you misunderstand how LEFT JOIN works. > 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? Imagine that Stats table contains one row (7, 42). Temp table contains two rows (42, 'question 1') and (42, 'question 2'). Your select statement would then return two rows, not one (do you understand why?) That's how you end up with more rows in the resultset than there are in Stats table. Igor Tandetnik _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
