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,

SubjectsHeading TEXT

)

CREATE TABLE Subjects_items

(

SubjectsItem INTEGER,

SubjectsIKey TEXT,

UNIQUE(SubjectsItem,SubjectsIKey)

)

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'

SubjectsKey SubjectsHeading

DOGS Dogs

CATS Cats

In the table 'Subjects_items'

SubjectsItem SubjectsIKey

10 DOGS

11 DOGS

12 CATS

13 DOGS

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.

Thanks.