Several one to many relationships in one query

  • Filter
  • Time
  • Show
Clear All
new posts

  • 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!