mysql - 根据列值连接多个表

Question

我有下表:

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 可能是:

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

我想返回以下列:

object_type, object_id, value, campaign, adset, ad

所以:

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

在上面的例子中它应该返回:

*************************************************************************
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
*************************************************************************

我不知道该怎么做。

我尝试过这样的事情:

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

但它不起作用，在事件 object_type 的情况下我也不需要进行连接。

Answer 1

UNION 是你的 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';