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 Leaving out the second join and only counting D.ROWID works all fine. Must be overlooking something simple, but can't see it and thanks for any advice. RBS
