Thanks, that works nicely indeed. I tried the first SQL, will try second as well. I wasn't aware of the construction with join without the fields to join on after the join keyword. It looks strange to me. I take it the joining fields are done in the select.
RBS On Sat, Feb 14, 2015 at 4:49 PM, Igor Tandetnik <igor at tandetnik.org> wrote: > On 2/14/2015 11:32 AM, Bart Smissaert wrote: > >> SELECT I.ITEM_NAME, COUNT(D.ROWID), COUNT(D2.ROWID) FROM ITEMS I >> INNER JOIN DESCRIPTIONS D ON (INSTR(D.FULL_TEXT, I.DEFINITION1) > 0) >> INNER JOIN DESCRIPTIONS D2 ON (INSTR(D2.FULL_TEXT, I.DEFINITION2) > 0) >> GROUP BY I.NAME >> > > You are doing a cross product of DESCRIPTIONS to itself. I expect the two > counts are the same, and are equal to a product of the actual, true counts. > > You are looking for something like this: > > select NAME, > sum(INSTR(FULL_TEXT, DEFINITION1) > 0), > sum(INSTR(FULL_TEXT, DEFINITION2) > 0), > from Items join Descriptions > group by NAME; > > -- or > > select NAME, > (select count(*) from Descriptions where INSTR(FULL_TEXT, DEFINITION1) > > 0), > (select count(*) from Descriptions where INSTR(FULL_TEXT, DEFINITION2) > > 0) > from Items; > > The first one would probably work faster. > -- > Igor Tandetnik > > _______________________________________________ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
