outer join problem

[verstapp]

The old 'how many detail lines does each master record have', with a twist.
I have 2 tables,
A: key, integer1, ...;
and
B: key, testdate, ...

Basically, A is master records, B is detail lines, related by A.key = B.key.
What I am looking for is a query generating:
testdate, counta
where testdate is every date in table B [suggesting a left join] and counta is a count of the number of records in table B with a particular testdate AND with a particular integer1 in table A.

The initial attempt, to produce
testdate, countdate [countdate just prints the number of detail lines for each testdate], looks like this:

SELECT B.testdate, count(B.testdate)
FROM B LEFT JOIN A ON (B.key = A.key)
GROUP BY testdate;

and produces correct results.
However, when I try to extend the query to take into account the value of A.integer1:

SELECT B.testdate, count(B.testdate)
FROM B LEFT JOIN A ON (B.key = A.key) AND (A.integer1 = 3)
GROUP BY testdate;

I get exactly the same results as before, incorrect in this case [I'm expecting lots of 0s].

It seems I'm missing something in my use of left join. Any ideas?

 

[r937]

SELECT B.testdate
     , count([red]A.key[/red])
  FROM B 
LEFT OUTER
  JOIN A 
    ON A.key = B.key
   AND A.integer1 = 3
GROUP 
    BY B.testdate

r937.com | rudy.ca

 

[verstapp]

Thanks, r937.

Exactly what I was looking for.