quote:

---

Originally posted by Albertinho

Hi all!

I'm playing around with a genealogy database.
I've got this table:

PERSON
----------------
Id Name ParentId
0 A NULL
1 B 0
2 C 1
3 D NULL
4 E 3
5 F 4
6 G 5


And now I'de like to find out if there is a relationship between two given PERSONs. For example, if I'd ask the database if there is a relationship between A and C, the answer would be true. However, if I'd ask if G is related to A, the answer would be false.

Maybe the database design is wrong? Is there another way of doing this, a best-practice when dealing with parent-child-relationships?

I've spent all day trying to solve this... so I would be very greatful for assistance.

Thanks!

---

Msg 240, Level 16, State 1, Line 2
Types don't match between the anchor and the recursive part in column "name2" of recursive query "retable".

;WITH retable (ID, name, name2, parentid)AS (
        SELECT  root.ID, root.NAME, convert(varchar(the data length of name column),''), root.PARENTID
        FROM    PERSON root
        WHERE   root.parentid is null
        UNION ALL
        SELECT  t.ID, t.NAME, r.NAME, t.PARENTID
        FROM    PERSON t , retable r where t.ParentID = r.ID
)
SELECT * FROM retable order by ID

where name='C' and name2='A'

;WITH retable (ID, name, name2, parentid)AS (
        SELECT  root.ID, root.NAME, convert(nvarchar(50),''), root.PARENTID
        FROM    PERSON root
        WHERE   root.parentid is null
        UNION ALL
        SELECT  t.ID, t.NAME, r.NAME, t.PARENTID
        FROM    PERSON t , retable r where t.ParentID = r.ID
)
SELECT * FROM retable 
WHERE name='C' AND name2='A'
order by ID

declare @Person as table(Id varchar(30), Name varchar(60), ParentId varchar(30))
insert into @Person
select '0', 'A', NULL union all
select '1', 'B', 0 union all
select '2', 'C', '1' union all
select '3', 'D', NULL union all
select '4', 'E', '3' union all
select '5', 'F', '4'


;with cte
as
(
select Name,Id,ParentId,convert(varchar(100),Name)as relationship from @Person F
union all
select Pe.Name,Pe.Id,Ct.ParentId,convert(varchar(100),Ct.relationship  + '>' + Pe.Name ) from @Person Pe  inner join cte Ct
on Ct.Id=Pe.ParentId

)

select Name,relationship from cte C
where C.ParentId IS NULL

	

--option (maxrecursion 0)

select case when name2 = 'A' then 'True' else 'False'
from retable where name = 'C'

quote:

---

Originally posted by Albertinho

Oops, I was a bit too fast.

I added one row:

where name='C' and name2='A'

Like this:

;WITH retable (ID, name, name2, parentid)AS (
        SELECT  root.ID, root.NAME, convert(nvarchar(50),''), root.PARENTID
        FROM    PERSON root
        WHERE   root.parentid is null
        UNION ALL
        SELECT  t.ID, t.NAME, r.NAME, t.PARENTID
        FROM    PERSON t , retable r where t.ParentID = r.ID
)
SELECT * FROM retable 
WHERE name='C' AND name2='A'
order by ID

...but it returns zero rows? Which means, the query works fine to check if B is related to A, but not to check if C is related to A.

---

declare @up1 varchar(1)
declare @up2 varchar(1)

set @up1 = 'D'
set @up2 = 'F'

;with cte
as
(
select Name, Id, ParentId, Name as relationship from Person F
where Name=@up1
union all
select Pe.Name, Pe.Id, Ct.ParentId, Ct.relationship from Person Pe 
inner join cte Ct
on Ct.Id=Pe.ParentId
)
select * from cte C
where Name=@up2 
order by C.Id