MySQL Performance: MySQL => Several one to many relationships in one query

Several one to many relationships in one query [message #1184]

Wed, 02 May 2007 09:45

razdaman
Messages: 26
Registered: May 2007

Junior Member

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_car
FROM cities AS c
LEFT JOIN buildings AS b ON b.city_id = c.city_id
LEFT 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!

Report message to a moderator