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!