How do I do mysql join statement on multiple table hierarchy levels?

Question

Suppose I have the following table house:

House:
id
name
cityID

Where cityID refers to the id field of table city

City:
id
name
stateID

where stateID refers to the id field of table state

State:
id
name
countryID

where countryID refers to the id field of the table country:

Country:
id
name

How do I do mysql join statements so that I can obtain houses within a specific country using this multiple level location reference hierarchy?

Answer 1

You can use joins or nested queries:

Select House.* from ((House join City on House.CityID = City.id)
                          join State on City.StateID = State.id)
                          join Country on State.CountryID = Country.id
               where Country.name = 'Australia'

or

Select * from House where CityID in (
    Select id from City where StateID in (
        Select id from State where CountryID in (
            Select id from Country where name = 'Australia'
        )
    )
)