The relationship is via the key (first field) in the first table 'Subjects' and the second field 'SubjectsIKey' in the second.
CREATE TABLE Subjects
SubjectsKey TEXT PRIMARY KEY ASC ON CONFLICT REPLACE,
CREATE TABLE Subjects_items
The first field in the first table is unique, no duplicates. There may be many 'items', however, which have the same subject entry and these appear in the second table. Example:
In the table 'Subjects'
In the table 'Subjects_items'
What I'd like to do is find out how many occurances of SubjectsIKey in the second table ('Subjects_items') match each SubjectsKey in the first table. The result is two fields: SubjectsKey and a value showing the count. In the example, DOGS 3 and then CATS 1.
I can do it using two SELECT statements, one to collect all the keys in the first table into an array or memory list then loop through them using another SELECT to count each heading, but my intuition says it should be possible using one single SELECT. The actual data could contain over 100,000 entries in Subjects and at least as many records in Subjects_items.
Hope this is clear.