SQL Count problem with Left Join

[lelij]

I've got 2 tables, Posting and Applications.

Applications is linked to Posting by its field "APID" in a relation Many Applications to One Posting.

Using mySQL, I'm trying to get 1 query that will list the fields from postings and count the number of active applications for each posting.

I tried :

SELECT Posting.*, Count(Applications.APID)
AS NbrCV
FROM Posting
LEFT JOIN Applications ON Posting.PID = Applications.APID
WHERE Applications.AStatus = '4'
GROUP BY Applications.APID

The problem is that this query omits Postings that do not have 0 Applications.

Any ideas ?

[Anon]

Try grouping by Posting.PID

Otherwise, what can it group on if there are no Applications.APID rows?

[lelij]

Thanks tomhath - that did the trick!

When can I buy you a beer ? 🙂

[Anon]

Having one now. Cheers.