Simple Query

[rafeman]

Hi there, can somebody show me how to get the data and explain the technique to do the following;

I have one table

Table A

PRIMARY PARENT_PRIMARY NAME

1 0 Sales
2 1 Food Sales
3 0 Cost of Sales
4 1 Drink Sales
5 3 Food Costs
6 3 Drink Costs

I need to create a query that shows the related parent primary name to each row. E.g.

PRIMARY PARENT_PRIMARY NAME PARENT_NAME

1 0 Sales null
2 1 Food Sales Sales
3 0 Cost of Sales null
4 1 Drink Sales Sales
5 3 Food Costs Cost of Sales
6 3 Drink Costs Cost of Sales

Thanks

 

[markros]

The technique is used "recursive CTE" (SQL Server 2005 and above).

;with cte as
(select Primary, Parent_Primary, [Name], null as [Parent_Name]
 from myTable where Parent = 0
union all
 select T.Primary, T.Parent_Primary, T.[Name], C.[Name] as [Parent_Name]
 from myTable T inner join cte C on T.Parent_Primary = C.Primary)

select * from cte

-----------------
From the top of my head (not tested).

Take a look at this blog (and blogs referenced in that blog):
CTE and hierarchical queries

PluralSight Learning Library