retrieving multiple rows with different ids in mysql

Question

i am trying to write a query in mysql, where my table structure is given below

place_id    state_id    city_id     place_name  
    1           0           0           United States   
    2           1           0           Alabama     
    3           0           2           Auburn  
    32          0           0           Canada  
    33          32          0           Alberta     
    34          0           33          Calgary

here i have united states id which is 1, using this id i want to retrieve all the states in united states and the cities of each state. Here,

if state_id and city_id is 0 then it is a country
if city_id is 0 then it is a state
if state_id is 0 then it is a city
place id is auto_increment

please some one help me, thank you.

Post your expected output.

Ravinder Reddy
– Ravinder Reddy

2017-03-09 10:05:40 +00:00
Commented Mar 9, 2017 at 10:05 — Ravinder Reddy
– Ravinder Reddy, Commented Mar 9, 2017 at 10:05
I don't envy you your data model. :-(

Strawberry
– Strawberry

2017-03-09 10:20:11 +00:00
Commented Mar 9, 2017 at 10:20 — Strawberry
– Strawberry, Commented Mar 9, 2017 at 10:20

Ullas · Accepted Answer · 2017-03-09 10:19:50Z

1

For United States you could use a self join for joining the same table 3 times.

select t1.place_name, t2.place_name, t3.
from my_table t1 
inner join my_table t2 on  t1.place_id = t2.state_id 
inner join my_table t3 on t3place_id =   t3.city_id
where t1.place_name  ='United States';

edited Mar 9, 2017 at 10:19

Ullas

11.6k5 gold badges36 silver badges52 bronze badges

answered Mar 9, 2017 at 10:05

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

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

1 Comment

Punabaka Abhinav Over a year ago

thank you for replying self joining solved my problem

Collectives™ on Stack Overflow

retrieving multiple rows with different ids in mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related