How to write JOIN QUERY for 4 tables in the below condition

Question

I have 4 tables ACCOUNTS_TABLE , LINKS_TABLE, GROUPS_TABLE, KEYS_TABLE

I need to get all accounts details which is of acct_type xx with count of Links, groups& keywords . I have tried this query but it gives all count as 0

SELECT 
    acc.acct_id, acc.acct_type, count(link.id) as link_count, link.account, 
    groups.camp_id, count(groups.id) as group_count, count(keyword.key_id) as key_count 

FROM ".ACCOUNTS_TABLE." as acc  
    LEFT JOIN ".LINKS_TABLE." as link ON link.account=acc.acct_id AND acct_type='xx' 
    LEFT JOIN  ".GROUPS_TABLE." as groups ON  groups.camp_id=link.id 
    LEFT JOIN ".KEYS_TABLE." as keyword ON keyword.camp_id=link.id 

GROUP BY acc.acct_id 

My required output should be like this

Any one please help me to slove this problem

Answer 1

You probably should use COUNT(DISTINCT ....).

SELECT acc.acct_id, COUNT(DISTINCT link.id), COUNT(DISTINCT groups.id), COUNT(DISTINCT keyword.key_id)
FROM ACCOUNTS_TABLE acc
LEFT OUTER JOIN LINKS_TABLE link ON link.account = acc.acct_id AND acct_type = 'advertiser'
LEFT OUTER JOIN GROUPS_TABLE groups ON  groups.camp_id = link.id 
LEFT JOIN KEYS_TABLE keyword ON keyword.id = link.id 
WHERE acc.acct_type = 'xx'
GROUP BY acc.acct_id

EDIT

Amended to use the updated join conditions, etc:-

SELECT acc.acct_id, acc.acct_type, COUNT( DISTINCT link.id ) , COUNT( DISTINCT groups.id ) , COUNT( DISTINCT keyword.key_id ) 
FROM ACCOUNTS_TABLE acc
LEFT OUTER JOIN LINKS_TABLE link ON link.account = acc.acct_id
LEFT OUTER JOIN GROUPS_TABLE groups ON groups.camp_id = link.id
LEFT JOIN KEYS_TABLE keyword ON keyword.camp_id=link.id 
WHERE acc.acct_type = 'xx'
GROUP BY acc.acct_id, acc.acct_type