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