JOINs on three tables - what structure is implied

[DaiLaughing]

I admit my brain is refusing to accept this.  Can someone clarify?

 

Using this query on the MySQL world database (http://dev.mysql.com/doc/index-other.html) what relational structure is implied:

 

SELECT *

FROM Country

LEFT OUTER JOIN CountryLanguage ON Country.Code = CountryLanguage.CountryCode

LEFT OUTER JOIN City ON Country.Code = City.CountryCode;

 

The implied structure of the data is that Country is the parent and both City and CountryLanguage are children of that parent:

 

            Country

              /          \

            /              \

          /                  \

      /                      \

CountryLanguage  City

 

What concerns me is that the way the query is written partly implies that CountryLanguage is a child of Country and City is a child of CountryLanguage:

 

Country

    |

    |

CountryLanguage

    |

    |

City

 

The reason my brain refuses to accept this is the "what if" question of how to deal with a situation where I actually want that three level parent/child structure.  For example:

 

Surgery

    |

    |

Doctor

    |

    |

Appointment

 

Do I then just change the ON part of the query to reflect that or does the structure of the JOIN part affect it also?

 

I have the feeling that there will be a face-palm moment when someone explains or even when I read my own question tomorrow!

[mikosiko]

then your better read your question again and prepare your palm:

What concerns me is that the way the query is written partly implies that CountryLanguage is a child of Country and City is a child of CountryLanguage:

 

Country

    |

    |

CountryLanguage

    |

    |

City

 

this select

 

SELECT * 
FROM Country
LEFT OUTER JOIN CountryLanguage ON Country.Code = CountryLanguage.CountryCode
LEFT OUTER JOIN City ON Country.Code = City.CountryCode;

 

doesn't imply that in any possible way... the relations are clearly established in the JOINS.

 

[DaiLaughing]

Only in the order of the query words I guess.

 

So how do i create a query that does use the structure in the surgery - doctor - appointment example (where the third table is a child of the second and not the first)?

 

Palm is poised.

[mikosiko]

let me answer you with a hypothetical situation using a variation of the Country-City-Language example:

SELECT Country.name, City.name, CityLanguage.languagename 
FROM Country
LEFT OUTER JOIN City ON City.CountryCode = Country.CountryCode
LEFT OUTER JOIN CityLanguage ON CityLanguage.CityCode = City.CityCode;

 

that could represent a master-child-child relation... apply the same for the surgery-doctor-appointment example

[DaiLaughing]

OK thanks both.  It is the ON part of the query which defines the structure not the JOINs themselves.