How to show recursive parentID in a single column in SQL

Question

Here is the example structure of the table:

ID    Name     ParentID
-----------------------
1     Ancestor      NULL
2     GrandFather   1
3     GrandMother   1
4     Child         3

I'm trying to write a query that would return

ID     Name        Family
----------------------------
 1     Ancestor 
 2     GrandFather Ancestor
 3     GrandMother Ancestor
 4     Child       Ancestor^GrandMother

The tricky part is that I want to show the family of all rows and in a top-down order.

If anyone can point me in the right direction, it would be appreciated :)

EDIT :: This is the real query, but it follows the same idea. it returns an error on line : marketparent.family + '^'+ t2.marketGroupName because it cant find marketparent

WITH marketparent ( marketGroupID,parentGroupID, marketGroupName,family)
AS
(
SELECT marketGroupID,
       parentGroupID,
       marketGroupName,
       '' as family 
 FROM EVE.dbo.invMarketGroups
 WHERE parentGroupID IS NULL
UNION ALL

    SELECT t2.parentGroupID,
     t2.marketGroupID,
     t2.marketGroupName,
     marketparent.family + '^'+ t2.marketGroupName
     FROM EVE.dbo.invMarketGroups as t2
     INNER JOIN marketparent as mp
     ON mp.marketGroupID = t2.parentGroupID
)

-- Statement using the CTE

SELECT TOP 10 *
FROM marketparent;

The solution will depend on the DBMS you use. SQL Server, Oracle, MySQL or ....? — Mikael Eriksson, Apr 16 '12 at 15:02

score 10 · Accepted Answer · answered Apr 16 '12 at 15:13

10

You did not specify your DBMS, so I'm assuming PostgreSQL

WITH RECURSIVE fam_tree (id, name, parent, family) as 
(
  SELECT id, 
         name, 
         parentid, 
         ''::text as family
  FROM the_unknown_table
  WHERE parent IS NULL

  UNION ALL

  SELECT t2.id, 
         t2.name, 
         t2.parentid, 
         fam_tree.family || '^' || t2.name
  FROM the_unknown_table t2 
     INNER JOIN fam_tree ON fam_tree.id = t2.parentid
)
SELECT *
FROM fam_tree;

This is standard SQL (except for the ::text typecast) that should work with very few changes on most modern DBMS.

Edit:

For SQL Server you would need to replace the standard concatention character with Microsoft's non-standar + (and you need to remove the recursive keyword which is required by the standard but for some strange reason rejected by SQL Server)

WITH fam_tree (id, name, parent, family) as 
(
  SELECT id, 
         name, 
         parentid, 
         '' as family
  FROM the_unknown_table
  WHERE parent IS NULL

  UNION ALL

  SELECT t2.id, 
         t2.name, 
         t2.parentid, 
         fam_tree.family + '^' + t2.name
  FROM the_unknown_table t2 
     INNER JOIN fam_tree ON fam_tree.id = t2.parentid
)
SELECT *
FROM fam_tree;

answered Apr 16 '12 at 15:13

now if i want to generate the family of a row which is in another table but has a foreign key to this table, can i just add some code in the last query or do i have to put something in the WITH ? – darthun08 Apr 16 '12 at 15:31
fam_tree.family + '^' + t2.name returns the error : The multi-part identifier "fam_tree.family" could not be bound. This is probably due to me not specifing the correct language in my question. I'm using MS SQL server 2008, and as for the rest, i don't know what information you would need :P – darthun08 Apr 16 '12 at 15:44
@user1336586: hmm, `fam_tree.family + '^' + t2.name` should work for all I know. As I don't have your tables, I assume some typo. Try removing the column definition in the with part: `with fam_tree as (...` – Apr 16 '12 at 16:15
`WITH marketparent ( marketGroupID,parentGroupID, marketGroupName,family) AS ( SELECT marketGroupID, parentGroupID, marketGroupName, '' as family FROM EVE.dbo.invMarketGroups WHERE parentGroupID IS NULL UNION ALL SELECT t2.parentGroupID, t2.marketGroupID, t2.marketGroupName, marketparent.family + '^'+ t2.marketGroupName FROM EVE.dbo.invMarketGroups as t2 INNER JOIN marketparent as mp ON mp.marketGroupID = t2.parentGroupID ) -- Statement using the CTE SELECT TOP 10 * FROM marketparent;` – darthun08 Apr 16 '12 at 16:34
found a website that explains a solution closer to what i'm looking for. basically the solution is to create another table, and alter it iteratively. http://www.sqlteam.com/article/more-trees-hierarchies-in-sql – darthun08 Apr 17 '12 at 05:13

score 0 · Answer 2 · answered Apr 16 '12 at 15:14

0

You can use a recursive Common Table Expression.

declare @T table
(
  ID int,
  Name nvarchar(15),
  ParentID int
);

insert into @T values
(1,     N'Ancestor',      NULL),
(2,     N'GrandFather',   1),
(3,     N'GrandMother',   1),
(4,     N'Child',         3);

with C as
(
  select T.ID,
         T.Name,
         T.ParentID,
         cast(N' ' as nvarchar(max)) as Family
  from @T as T
  where T.ParentID is null
  union all
  select T.ID,
         T.Name,
         T.ParentID,
         C.Family+'^'+C.Name
  from @T as T
    inner join C
      on T.ParentID = C.ID
)
select C.ID,
       C.Name,
       stuff(C.Family, 1, 2, '') as Family
from C;

answered Apr 16 '12 at 15:14

Mikael Eriksson

136,425
22
210
281

@user did this work out for you? I see that you had some issues with the accepted answer that I believe is taken care of in my answer. – Mikael Eriksson Apr 16 '12 at 19:59
no it didn't work. I found another solution where i create a new table and update the values in a iterative way. If you have time, please search for my new post, i still need help with the same thing. – darthun08 Apr 20 '12 at 13:32
@user1336586 - As far as I can tell this gives you exactly what you want. Try here. http://data.stackexchange.com/stackoverflow/query/67361 What is problems do you have with this solution. – Mikael Eriksson Apr 20 '12 at 14:04
indeed it works. but i don't understand everything about it. I know your solution is the one microsoft also recommends on their website. but after trying something like that for a few days, finding another one, and making it work in 2 days only was better for me . But i'd still recommend your way to anyone who has the same problem i had. Thank you for your help Mikael Eriksson, and for following the topic to it's end :) – darthun08 Apr 21 '12 at 02:15

score 0 · Answer 3 · answered Apr 16 '12 at 15:18

0

select T.ID, T.Name, (select name from table where ID=T.ParentID)as Family
from table T

answered Apr 16 '12 at 15:18

Diego

34,802
21
91
134

your query seems to only return the parent name of every row. it's a nice try, but i'm looking for the full path :) – darthun08 Apr 16 '12 at 16:01

How to show recursive parentID in a single column in SQL

3 Answers3