Simple SQL - or not? A 5 min problem!

[klwemu]

First I thought, a job for 5 minutes, but I cant solve it ..... )

The simple requirement: 2 Tables
1Tab contains:
A
B
C
D

2 Tab contains:
A red
B red
B red
A green
B green
D red

What i want is to list ALL Tab1 entryes and count them (not with
Dcount) in Tab 2 in one Query, filterd to one selectable colour : >>
RED =
A 1
B 2
C 0
D 1

Thanks .....

 

[Ken Snell \(MVP\)]

SELECT [Enter color name:], Table1.FieldName,
(SELECT Count(*) FROM Table2
WHERE Table2.Field1 = Table1.FieldName
AND Table2.Field2 = [Enter color name:]) AS HowManyIn2
FROM Table1;

 

[Jamie Collins]

SELECT [Enter color name:], Table1.FieldName,
(SELECT Count(*) FROM Table2
WHERE Table2.Field1 = Table1.FieldName
AND Table2.Field2 = [Enter color name:]) AS HowManyIn2
FROM Table1;

Alternatively, trying to avoid the correlated subquery:

SELECT T1.FieldName,
IIF(DT1.HowManyIn2 IS NULL, 0, DT1.HowManyIn2) AS HowManyIn2
FROM Tab1 AS T1
LEFT JOIN (
SELECT T2.FieldName, COUNT(*) AS HowManyIn2
FROM Tab2 AS T2
WHERE T2.Field2 = [Enter color name:]
GROUP BY T2.FieldName
) AS DT1
ON T1.FieldName = DT1.FieldName;

Jamie.

--

 

[Jamie Collins]

Alternatively, trying to avoid the correlated subquery <<snipped>>

Doh! It *is* simple:

SELECT T1.FieldName,
SUM(IIF(T2.Field2 = [Enter color name:], 1, 0)) AS HowManyIn2
FROM Tab1 AS T1
LEFT JOIN Tab2 AS T2
ON T1.FieldName = T2.FieldName
GROUP BY T1.FieldName;

Jamie.

--

 

[Ken Snell \(MVP\)]

I like this one!
--

Ken Snell
<MS ACCESS MVP>

Alternatively, trying to avoid the correlated subquery <<snipped>>

Doh! It *is* simple:

SELECT T1.FieldName,
SUM(IIF(T2.Field2 = [Enter color name:], 1, 0)) AS HowManyIn2
FROM Tab1 AS T1
LEFT JOIN Tab2 AS T2
ON T1.FieldName = T2.FieldName
GROUP BY T1.FieldName;

Jamie.