MySQL:根据多行数据合并多个相关表

Question

[已更新]

我有三个表，它们由一个表定义，这三个表之间的关系由另一个表定义，如下所示:

1. 表 #1:wp_pods_cities [城市 PODS]

   item_id  city
   -------- ----------
   1        Albany
   2        Perth
   3        Albany
   4        Hollywood
   5        Albany
   6        San Diego
   7        Denpasar
   

2. 表 #2:wp_pods_regions [区域 PODS]

   item_id  region
   -------- ------------------
   1        Western Australia
   2        California
   3        Texas
   4        Bali
   

3. 表 #3:wp_pods_countries [国家/地区 PODS]

   item_id  country
   -------- --------------
   1        Australia
   2        United States
   3        Indonesia
   

4. 表 #4:wp_posts [帖子类型]

   pod_id  pod_name   post_type
   ------- ---------- ----------
   pod_1   countries  pods
   pod_2   regions    pods
   pod_3   cities     pods
   

5. 表 #5:wp_podsrel [PODS 关系]

   rel_id  pod_id  item_id  related_pod_id  related_item_id
   ------- ------- -------- --------------- ----------------
   1       pod_2   1        pod_1           1 
   2       pod_2   2        pod_1           2 
   3       pod_2   3        pod_1           2 
   4       pod_2   4        pod_1           3 
   5       pod_3   1        pod_2           1 
   6       pod_3   2        pod_2           1 
   7       pod_3   3        pod_2           2 
   8       pod_3   4        pod_2           2 
   9       pod_3   5        pod_2           3 
   10      pod_3   6        pod_2           3
   11      pod_3   7        pod_2           4
   

NOTE: The tables above are created in Wordpress by PODS CMS plugin but are simplified to make them readable here. The actual tables also have thousands of data.

我想做什么

我想列出定义的相同城市(在本例中为奥尔巴尼)以及地区，因此< strong>他们所属的国家。

• 预期结果:

  city    region             country
  ------- ------------------ --------------
  Albany  Western Australia  Australia
  Albany  California         United States
  Albany  Texas              United States
  

我尝试过的

SELECT ct.city, rg.region, ctr.country
FROM wp_pods_cities AS ct
INNER JOIN wp_podsrel AS rel ON ct.item_id = rel.item_id
AND rel.pod_id IN ('pod_3', 'pod_2')
INNER JOIN wp_pods_regions AS rg ON rel.related_item_id = rg.item_id
INNER JOIN wp_pods_countries AS ctr ON rel.related_item_id = ctr.item_id
WHERE ct.city = 'Albany'
ORDER BY ct.city

这将为我提供城市及其相关地区，但国家不相关。如何获得上面的示例结果？

提前致谢。

Answer 1

我成功地通过使用 podsrel 表两次获得了正确的输出，一次针对城市，一次针对地区。

SELECT ct.city, rg.region, co.country

FROM wp_pods_cities AS ct, wp_podsrel AS pr

INNER JOIN wp_pods_regions AS rg
    ON rg.item_id = pr.related_item_id

INNER JOIN wp_podsrel AS pr2
    ON pr2.item_id = rg.item_id

INNER JOIN wp_pods_countries AS co
    ON co.item_id = pr2.related_item_id

WHERE ct.city = 'Albany'
    AND pr.item_id = ct.item_id

ORDER BY ct.city