Search code examples
mysqldatabasewordpressrowpodscms

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


[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:

  1. 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
    
  2. Table #2: wp_pods_regions [Regions PODS]

    item_id  region
    -------- ------------------
    1        Western Australia
    2        California
    3        Texas
    4        Bali
    
  3. Table #3: wp_pods_countries [Countries PODS]

    item_id  country
    -------- --------------
    1        Australia
    2        United States
    3        Indonesia
    
  4. Table #4: wp_posts [Post Types]

    pod_id  pod_name   post_type
    ------- ---------- ----------
    pod_1   countries  pods
    pod_2   regions    pods
    pod_3   cities     pods
    
  5. 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.


Solution

  • 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