Retrieval of hierarchial data

Question

I have a region code to select area based on type in one query.

Table place:

regionname regiontype placeid parentplaceid
-------------------------------------------
Chennai    city        4        2
Bangalore  city        3        1
TamilNadu  state       2        2
Karnataka  state       1        1

Table Country:

countryname State

India       TamilNadu
India       Karnataka

select  c.countryname, p.regionname, c.State
from place p,
     country c
where p.regionname = c.State

Expected:

India       TamilNadu   TamilNadu
India       Karnataka   Karnataka
India       Chennai     TamilNadu
India       Bangalore   Karnataka

Actual

India       TamilNadu   TamilNadu
India       Karnataka   Karnataka

Why do you expect a result with the values "Bangalore" and "Karnataka" on a row, when in your condition you filter out rows where those are not equal? — vlumi, Jul 25 '19 at 05:49
banglore is a city in karnataka, it uses parentplace as id regardless where there it is a city, town or state.Every city and town has parentplace pointing to the the state it belong and state has its own id. — user11366750, Jul 25 '19 at 05:50
What is the column p.placename? It's not on your table definition. — vlumi, Jul 25 '19 at 05:52
You should probably split your place table into state and city tables, as that would form a more natural hierarchy of country > state > city. — vlumi, Jul 25 '19 at 05:54
i have corrected it now.thanks, purpose is to use placeid and parentplaceid in a single select to retrive — user11366750, Jul 25 '19 at 05:54
Tip of today: Always use modern, explicit `JOIN` syntax. Easier to write (without errors), easier to read (and maintain), and easier to convert to outer join if needed. — jarlh, Jul 25 '19 at 06:03
https://stackoverflow.com/questions/tagged/sql+hierarchical-data+recursive-query — , Jul 25 '19 at 06:26

score 0 · Accepted Answer · answered Jul 25 '19 at 11:43

This seems rather complicated. You can do what you want using union all -- one part for the states and one for the cities.

select c.countryname, p.regionname, p.state
from place p join
     country c
     on p.regiontype = 'state' and
        p.regionname = c.state
union all
select c.countryname, p.regionname, ps.state
from place p join
     place ps
     on p.parentplaceid = ps.placeid and
        p.regiontype = 'city' and
        ps.regiontype = 'state' join
     country c
     on ps.regionname = c.state;

Retrieval of hierarchial data

1 Answers1