What is the correct SQL statement for has-many-through joins on multiple classes with single table inheritance

Question

I have two tables, Places and Users

-- Places Table: --
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255) | NO   |     | NULL    |                |
| type       | varchar(15)  | NO   |     | NULL    |                |
| parent_id  | int(11)      | YES  | MUL | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

-- Users Table: --
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255) | NO   |     | NULL    |                |
| city_id    | int(11)      | YES  | MUL | NULL    |                |
+------------+--------------+------+-----+---------+----------------+

I have three models that all use the Places table and all are subclasses of the Place model. The places.type column holds the class name of the subclass so I know which model it belongs to.

Country, State, City.

So, the inheritence structure is as follows:

Country < Place State < Place City < Place

The relationships are as follows:

Country has many States State has many Cities City has many Users

I want to return all Users within a given Country by joining through the state and city but I can't get the SQL query to work for it.

Any SQL gurus out there who can point me in the right direction?

score 0 · Answer 1 · answered Oct 14 '13 at 03:42

How about this? You'll need the id of the country.

SELECT st.name,  
       city.name,  
       u.name  

FROM   users u,  
       places city,  
       places st  

WHERE  u.city_id = city.id  
AND    city.type = 'city'  
AND    st.type = 'state'  
AND    st.id = city.parent_id  
AND    st.parent_id = @country_id

What is the correct SQL statement for has-many-through joins on multiple classes with single table inheritance

1 Answers1