Several one to many relationships in one query

Hi guys,

I have a problem that is a bit hard to explain, but I’ve made a simple example, that illustrates it.

Imagine if you have three tables: cities, buildings, cars

Each building and each car belongs to a city. So there is two one-to-many-relationships. City -> Cars and City -> Buildings.

My problem now is, that I want to get all information about all cities. I want to select all of its cars and buildings.

I can do this with the following query:

SELECT c.city_id, c.name, b.name AS name_building, ca.name AS name_carFROM cities AS cLEFT JOIN buildings AS b ON b.city_id = c.city_idLEFT JOIN cars AS ca ON ca.city_id = c.city_id

This will give me all information about a city. But since there is no direct relationship between buildings and cars, all “car-rows” will be joined with all “building-rows” - hence the query will return [number of building] * [number of cars] for EACH city! If you have fx 100 cars and 100 buildings in each of 100 cities… you’ll end up with something like 100^3 rows!

I hope you understand my problem. Is there a smart way to solve it? Or do I just have to deal with it?

And by the way - I’m not able to put car-data and building-data in the same table as the actual data structure is a bit more complex than in this little example.

Alternatives are very welcome…

Thank you!