| 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!
|
|
|
Members
Search
Help
Register
Login
Home
