MySQL skip one child level

Question

I have a table that has location ids in parent child relationships. It is basically a hierarchy like:

State
  -County
     --City

The table maps the hierarchy with the following columns

parentID
childID
type

If I want to get all sub-locations for a given location that's straightforward, something like:

SELECT childID FROM locations_table WHERE parentID = 1234 AND type = 'county'

But, if I need to skip one child level (e.g. skip the county level and include just the cities for a specific state), I can't figure out what SQL would work best to do that.

Basically, I am trying to get the children of the children for a specific parentID but can't figure out how to get that in a single SQL statement.

Answer 1

Use a self-join

SELECT childID
FROM locations_table T1
JOIN locations_table T2 ON T1.parentid = T2.childid
WHERE T2.parentID = 1234 AND T1.type = 'county'

Answer 2

SELECT city.childID FROM
  locations_table INNER JOIN
  locations_table AS county ON locations_table.childID = county.parentID 
    INNER JOIN
  locations_table AS city ON county.childID = city.parentID
  where city.type='city' and locations_table.parentid=1234