MySQL: Combine multiple related tables based on data in multiple rows

Question

[UPDATED]

I have three tables that are defined by one table and the relationship between those three tables are defined by another table like below:

Table #1: wp_pods_cities [Cities PODS]

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

Table #2: wp_pods_regions [Regions PODS]

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

Table #3: wp_pods_countries [Countries PODS]

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

Table #4: wp_posts [Post Types]

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

Table #5: wp_podsrel [PODS Relationship]

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.

What I'd Like To Do

I would like to list the same cities which are defined (in this case, Albany) as well as the regions and thus the countries they belong to.

expected result:

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

What I've Tried

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

This will get me the cities and their related regions but the countries aren't related. How do I achieve the sample result above?

Thanks in advanced.

score 1 · Answer 1 · answered Feb 23 '13 at 13:16

1

I managed to get the correct output by using the podsrel table twice, once for city, once for region.

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

answered Feb 23 '13 at 13:16

sjdaws

3,466
16
20

Hmmm... The code works for the **sample tables above**, but when I tried it on my **actual database**, it returned more amount of the same cities, some have the right region/country relationship, some others don't. I'm not really sure why. Still checking... Thanks for the reply, though. – Giraldi Feb 24 '13 at 01:13
I'm really sorry. It's my fault. I actually tried to modify the `item_id` columns so that they would be readable and recognizable by making them **unique**. But this seems to give the wrong implications that had lead to a different solution. Sorry about that. The `item_id`'s should not have any **prefix**. I will update the question. – Giraldi Feb 24 '13 at 01:22
With the **updated** question, the code above resulted like **[this](http://www.sqlfiddle.com/#!2/d40c0/1)**. Anyway to improve this solution? I can see it's heading in the right direction. Cheers! – Giraldi Feb 24 '13 at 01:31

score 0 · Accepted Answer · answered Feb 24 '13 at 03:26

Thanks to @sjdaws's input I managed to modify his solution for my updated question.

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
        AND pr.pod_id = 'pod_3'
        AND pr2.item_id = rg.item_id
        AND pr2.pod_id = 'pod_2'
    ORDER BY co.country, rg.region

Check out the result here: http://www.sqlfiddle.com/#!2/d40c0/10

MySQL: Combine multiple related tables based on data in multiple rows

What I'd Like To Do

What I've Tried

2 Answers2