I have three tables: organizations (id, org_name), locations (id, loc_name, parent_id), and organizations_locations (org_id, loc_id).
The table ‘locations’ contains towns, regions, provinces, country. If town A is in region B, then the id of region B is recorded as the parent_id of town A.
I can’t figure out how to write a query that returns the names of all the organizations in a given location, and also in the location’s parent location.
Is there maybe a better way to organize my tables that will make this easier?
SELECT o.org_name
FROM locations AS l
INNER
JOIN organizations_locations AS ol
ON ol.loc_id = l.id
INNER
JOIN organizations AS o
ON o.id = ol.org_id
WHERE l.loc_name = 'Toronto'
names of all organizations in a given location’s parent location –
SELECT o.org_name
FROM locations AS l
INNER
JOIN locations AS pl
ON pl.id = l.parent_id
INNER
JOIN organizations_locations AS ol
ON ol.loc_id = pl.id
INNER
JOIN organizations AS o
ON o.id = ol.org_id
WHERE l.loc_name = 'Toronto'
Thanks, @r937. Those two basic queries I got. It was the UNIONing of them that I didn’t know how to do. I’ve never connected two levels like that before. I won’t be able to get back to this until Monday to see if I can get it to work.
Basically, an organization is linked to locations that it services. It may be a town or two, it may be just a region. But if I search for organizations that service a town, I want it to also pull up organizations that service the region that the town is in.
If the organization services a region, I don’t want the person populating the database to have to enter every single town in that region, especially because a new town may be added later.