dean-hodgson (10/9/2012)
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)
)
...
Is this really the DDL for the tables? Are you really on SQL Server, version 7 or 2000?
Here are three different ways to get the results you are looking for, the last one will only work on 2k5 and above:
SELECT s.SubjectsKey, CountPerKey = ISNULL(d.CountPerKey,0)
FROM Subjects s
LEFT JOIN (
SELECT SubjectsIKey, CountPerKey = COUNT(*)
FROM Subjects_items
GROUP BY SubjectsIKey
) d ON d.SubjectsIKey = s.SubjectsKey
SELECT s.SubjectsKey, CountPerKey = COUNT(i.SubjectsIKey)
FROM Subjects s
LEFT JOIN Subjects_items i
ON i.SubjectsIKey = s.SubjectsKey
GROUP BY s.SubjectsKey
SELECT s.SubjectsKey, CountPerKey = ISNULL(d.CountPerKey,0)
FROM Subjects s
OUTER APPLY (
SELECT SubjectsIKey, CountPerKey = COUNT(*)
FROM Subjects_items i
WHERE i.SubjectsIKey = s.SubjectsKey
GROUP BY SubjectsIKey
) d
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]