DB2 SQL count in join or where clause

Question

This is probably an amateur question but I'm an amateur ! ;o)

I have several tables: Account table, Officer table, Location table, Web table... The Officer table doesn't give me the number of officers per account.

I need to retrieve only the accounts that have more than 20 officers. Should I use the COUNT ? If so, how ?

Your help is greatly appreciated. Thanks.

Pat

Answer 1

Update:

select a.id, a.eff-date, l.address, l.city, c.phonenumber
from Account a 
left outer join location l on a.id = l.id
left outer join contact c on a.id = c.id
where a.id in (
    select a.id
    from Account a
    inner join Officer ao on a.id = ao.id
    group by a.id
    having count(*) > 20    
)