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

I'm not sure to understand the question: you'd like to recursively lookup the table to print out the complete child->parent hierarchy? — Shastick, Nov 02 '16 at 18:49
I think you just need to add `ly2.name` and `ly3.name` to your first SQL statement's SELECT list and you'll be golden. You join those tables in but then you don't select from them... `Select distinct bu.id,bu.parent_id,bu.name, ly2.name, ly3.name` — JNevill, Nov 02 '16 at 18:55
This is for SQL server, I have edited the question to let it make sense. — Todayboy, Nov 02 '16 at 20:14
You need look like tree tables. Similar question here: http://stackoverflow.com/questions/5522478/sql-query-for-tree-table — mkysoft, Nov 02 '16 at 20:26
This is recursive table queries, Common Table Expression will help you. please see this https://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx — Tony Dong, Nov 02 '16 at 20:28
Thanks for your reply! But all I want is make it shorter. By the 2 websites provided, only make the query longer as still using UNION ALL and gave a new column name to it. — Todayboy, Nov 02 '16 at 21:05
This seems like poor design to me. Why on earth would you want to keep different types of data in the same table? — ATC, Nov 03 '16 at 07:48
your question doesn't make sense. You should explain it in more detail — Fahmi, Nov 03 '16 at 09:10

score 2 · Answer 1 · answered Mar 23 '17 at 10:08

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

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

1 Answers1