Dejell Dejell - 5 months ago 19
MySQL Question

Joining from multiple tables based on column value

I have the following tables:

objects:
**************************************************
object_type | object_name | object_id | value
campaign | my_campaign | 1 | my_value
ad set | my_adset | 1 | adset_value
ad | my_ad | 1 | ad_value
**************************************************

campaigns:
******************
id | name
1 | my_campaign
******************

adsets:
***************************
id | name | campaign.id
1 | my_adset | 1
***************************

ads:
**********************
id | name | adset.id
1 | my_ad | 1
**********************


object_id
in objects could be:

campaigns.id if object_type = "campaign"
adsets.id if object_type = "ad set"
ads.id if object_type = "ad".


I would like to return the following columns:

object_type, object_id, value, campaign, adset, ad


so:

if object_type is:
"campaign": then campaign=object_name, adset = "", ad =""
"adset": then campaign = adsets.campaign_id, adset = object_name, ad=""
"ad": then campaign = adsets.campaign_id, adset = ad.adset.id, ads = object_name


in above example it should return:

*************************************************************************
object_type | object_id | value | campaign | adset | ad
campaign | 1 | my_value | my_campaign | |
ad set | 1 | adset_value | my_campaign | my_adset |
ad | 1 | ad_value | my_campaign | my_adset | my_ad
*************************************************************************


I am not sure how to do it.

I tried something like this:

select
object_type,
object_id,
value,
campaigns.name as campaign,
adsets.name as adset,
ads.name as ads
from
objects, campaigns, adsets
Left outer join ads on
adsets.campaign_id = campaigns.id and ads.adset_id = adsets.id and objects.object_id = ads.id
left outer join adsets on
adsets.campaign_id = campaigns.id and objects.object_id = ads.adsets


but it doesn't work, also in case of campaign object_type I don't need to do the join.

Answer

UNION is your friend:

SELECT o.object_type, o.object_id, o.value, c.name, s.name, a.name
   FROM objects o
        JOIN ads a ON o.object_id = a.id
        JOIN adsets s ON a.adset_id = s.id
        JOIN campaigns c ON s.campaign_id = c.id
   WHERE o.object_type = 'ad'
UNION SELECT o.object_type, o.object_id, o.value, c.name, s.name, NULL
   FROM objects o
        JOIN adsets s ON o.object_id = s.id
        JOIN campaigns c ON s.campaign_id = c.id
   WHERE o.object_type = 'ad set'
UNION SELECT o.object_type, o.object_id, o.value, c.name, NULL, NULL
   FROM objects o
        JOIN campaigns c ON o.object_id = c.id
   WHERE o.object_type = 'campaign';