I've got these four database tables:
civilizations(
civilization_id PRIMARY KEY
civilization_name
civilization_leader
)
cities(
city_id PRIMARY KEY
civilization_id REFERENCES civilizations.civilization_id
city_name
city_population
)
city_buildings(
city_building_id PRIMARY KEY
city_building_name
city_id REFERENCES cities.city_id
building_id REFERENCES buildings.building_id
)
buildings(
building_id PRIMARY KEY
building_name
)
I want to write a SELECT statement that displays the name of each individual building (stored in city_building_name), the type of building (stored in building_name), the name of the city it's located in (stored in city_name), and the name of the civilization which controls that city (stored in civilization_name).
Here's what I've got so far:
SELECT city_buildings.city_building_name, buildings.building_name, cities.city_name
FROM city_buildings
INNER JOIN buildings ON city_buildings.building_id = buildings.building_id
INNER JOIN cities ON city_buildings.city_id = cities.city_id
That query works and gets me the first three of my required fields, but I don't know how to also get the names of the civilizations which control each city, because the city_buildings table that I've specified after FROM doesn't directly reference the civilizations table with the civilization_name field in it, and I don't know how to write an INNER JOIN that will bypass the cities table and get the value I need from the civilizations table.
The relational database management system I'm running is MySQL. Please could any full answers be posted as proper replies, not just as comments under my question post (but any questions are fine in comments). Thanks.
You can use the civilization_id from the cities table to join with the civilizations table and get the name of the civilization from there.
SELECT
cb.city_building_name,
b.building_name,
ct.city_name,
civ.civilization_name
FROM city_buildings cb
INNER JOIN buildings b
ON cb.building_id = b.building_id
INNER JOIN cities ct
ON cb.city_id = ct.city_id
INNER JOIN civ
ON civ.civilization_id = ct.civilization_id
In long joins sometimes is useful to use aliases to make the query more readable.
A table reference can be aliased using tbl_name AS alias_name or tbl_name alias_name: