Many to many query with a count of condition > 0

Question

Hi everyone,

I have 3 tables:

• Emails
• Foo
• EmailFoos (The many to many join table)

Foo can be complete or not.

I need to find the set of emails where the count of completed foos > 0 (and the inverse, but I can probably do that ;)

I tried something like:

SELECT e.id, e.address, count(l.foo_id) as foo_count 
FROM  emails e 
LEFT JOIN   (
   SELECT  l.foo_id, l.email_id 
   FROM  foo_emails l 
   JOIN  foos f ON (l.foo_id = f.id AND f.status = 'complete')) l ON 
(l.email_id = e.id) 
GROUP BY  e.id 
HAVING foo_count > 0;

But it keeps telling me that foo_count does not exist. My SQL_FU is weak. Postgress is the DBMS. Any help is much appreciated.

Answer 1

I'm pretty sure you just need to replace the foo_count in the having with count(l.foo_id).