On 2/14/2015 6:32 PM, Bart Smissaert wrote: > Having problems with the following SQL: > > 2 tables, trying to count occurrence of field 2 and field 3 of table 1 > in field 1 (only field) of table 2. > > Table 1 called ITEM with fields: NAME, DEFINITION1, DEFINITION2 > all text fields. Values in NAME are all unique. > > Table 2 called DESCRIPTIONS with only one field, FULL_TEXT also a text > column. > > This is the SQL that I think should work but doesn't as it takes very long > and produces > too high counts: > > 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
I haven't tested it, but my quick-scan answer would be that you cannot refer Descriptions twice like that. You are essentially asking the engine to match every entry in table DESCRIPTIONS (D2) to every entry in the same DESCRIPTIONS (D1) and then multiply it by matching to every entry in ITEMS and keep those where an INSTR functions succeeds - something that cannot be optimized out by the QP - i.e. which all will take very long. If you really want the total number of times a definition for Name in either the first or second definition, then these SQL statements might work: SELECT I.ITEM_NAME, COUNT() FROM ITEMS I INNER JOIN DESCRIPTIONS D ON (INSTR(D.FULL_TEXT, I.DEFINITION1) > 0) OR (INSTR(D.FULL_TEXT, I.DEFINITION2) > 0) GROUP BY I.NAME If however you want the total amount of times separate for Definitions 1 and 2, then some other method is needed, like this possibility: SELECT * FROM ( SELECT I.ITEM_NAME, 1 AS Kind, COUNT() FROM ITEMS I INNER JOIN DESCRIPTIONS D ON (INSTR(D.FULL_TEXT, I.DEFINITION1) > 0) UNION ALL SELECT I.ITEM_NAME, 2 AS Kind, COUNT() FROM ITEMS I INNER JOIN DESCRIPTIONS D ON (INSTR(D.FULL_TEXT, I.DEFINITION2) > 0) ) GROUP BY I.NAME, Kind And finally, to achieve the exact thing you asked for (but I think not efficiently): SELECT I.ITEM_NAME, COUNT(CASE INSTR(D.FULL_TEXT, I.DEFINITION1) WHEN 0 THEN 0 ELSE 1 END)) AS CountDef1 COUNT(CASE INSTR(D.FULL_TEXT, I.DEFINITION2) WHEN 0 THEN 0 ELSE 1 END)) AS CountDef2 FROM ITEMS I JOIN DESCRIPTIONS D GROUP BY I.NAME or you can use a boolean short-cut (because TRUE evaluates to 1) to achieve the exact same with: SELECT I.ITEM_NAME, COUNT(INSTR(D.FULL_TEXT, I.DEFINITION1)>0) AS CountDef1 COUNT(INSTR(D.FULL_TEXT, I.DEFINITION2)>0) AS CountDef2 FROM ITEMS I JOIN DESCRIPTIONS D GROUP BY I.NAME If this doesn't work for you, or if any of this works for you and you are not sure howcome it specifically works, feel free to ask and I (or someone else here) will be glad to dissect it.
