SQL Server: select same column 3 times into 1 column without UNION ALL

Question

I have a table called placeTable totally have three columns: id, parent_id, name, it included country, state and city table.

The parent_ID of the city is id of state, and parent_ID of state is id of country.

And I have another table which called cityList has specified city which id is same as placeTable's ID.

All I want is only select the city stated at Table2 cityList inluding its state and country in Table 1. For example below, Table 2 showed Sydney, so I want to get id, parent_ID and name of Sydney this city, and its state NSW and its country Australia in table1.

• Table1: placeTable

 id parent_ID name
 -------------------
 1      0     Australia 
 2      0     UK
 33     1     NSW
 34     1     Western Australia     
 55    33     Sydney
 70    34     Perth

• Table2: cityList

id        name 
-------------------
55      Sydney

The output I want:

 id parent_ID name
-------------------
 1      0     Australia 
 33     1     NSW 
 55    33     Sydney

I can get my desired results by doing below but too long, I am thinking there may be other smarter ways available:

Select distinct pt.id,pt.parent_id,pt.name 
from placeTable AS pt
join cityList as cl on cl.id = pt.id
UNION ALL
Select distinct ly2.id,ly2.parent_id,ly2.name 
from placeTable AS pt
join cityList as cl on cl.id = pt.id
join placeTable AS ly2 on pt.parent_id = ly2.id
UNION ALL
Select distinct ly3.id,ly3.parent_id,ly3.name 
from placeTable AS pt
join cityList as cl on cl.id = pt.id
join placeTable AS ly2 on pt.parent_id = ly2.id
join placeTable as ly3 on ly2.parent_id = ly3.id

I have tried below, but this can give me only city:

Select distinct pt.id,pt.parent_id,pt.name 
from placeTable AS pt
join cityList as cl on cl.id = pt.id
join placeTable AS ly2 on pt.parent_id = ly2.id
join placeTable as ly3 on ly2.parent_id = ly3.id

Answer 1

Please Try this:

            create table #placeTable (ID int, ParentID int, Name varchar(20))
            insert into #placeTable (ID, ParentID, Name)
            values (1,0,'Australia'), (2,0,'UK'), (33,1,'NSW'), (34,1,'Western Australia'),
            (55,33,'Sydney'),(70,34,'Perth');

            create table #cityList (ID int, Name varchar(20))
            insert into #cityList (ID, Name)
            values (55, 'Sydney'), (70, 'Perth');

            WITH Selects AS (
            SELECT p.*, c.ID as 'GroupID'
            FROM    #placeTable p
            INNER JOIN #cityList c on p.ID = c.ID

            UNION ALL
            SELECT  p.*, s.GroupID
            FROM    #placeTable p 
            INNER JOIN Selects s ON p.ID = s.ParentID
            )
            SELECT ID, ParentID, Name FROM Selects ORDER BY GroupID, ID