mySQL query joining five tables

Vote count:

0

I have the following five tables (some fields omitted for brevity)

(campaign)
campaign_id
===========
campaign_name
campaign_description


(ad_group)
campaign_id
===========
group_id
===========
group_name


(used_ad)
campaign_id
===========
group_id
===========
ad_id
===========


(billboard_group)
campaign_id
===========
group_id
===========
group_name


(used_billboard)
campaign_id
==============
group_id
==============
billboard_code
==============

So a "campaign" is made up of 1) an "ad_group", and 2) a "billboard_group". The ad_group contains many ads (stored in the "used_ad" group), and the billboard_group contains many billboards (stored in the "used_billboard" group).

I want to run a query that will return all of the campaigns, and list the total number of ads and billboards that are related to that campaign. I've been able to do this for ad_group only, but not ad_group AND billboard_group.

For example, this query:

SELECT campaign.*, COUNT(used_ad.ad_id) AS used_ads
FROM campaign
LEFT JOIN ad_group 
  ON campaign.campaign_id = ad_group.campaign_id
LEFT JOIN used_ad
  ON ad_group.group_id = used_ad.group_id
  AND used_ad.campaign_id = campaign.campaign_id
GROUP BY campaign.campaign_id

Gives a listing like this:

campaign_id     campaign_name     campaign_description    used_ads
1               Test campaign     Testing                 4
2               Second campaign   Second                  0

But what I'm after is:

campaign_id     campaign_name     campaign_description    used_ads    used_billboards
1               Test campaign     Testing                 4           5
2               Second campaign   Second                  0           3

Only I can't seem to figure out how to include counts from BOTH used_ad and used_billboard. I'd be grateful for any help.

asked 1 min ago

AMarch

19

mySQL query joining five tables