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?

Andrew Cooper · Accepted Answer · 2010-09-17 01:56:54Z

2

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'
        )
    )
)

answered Sep 17, 2010 at 1:56

Andrew Cooper

32.7k5 gold badges86 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related