SQLTeam.com | Weblogs | Forums

Query Help


#1

I need a query to get ParentchildtreeDesc values to the the expected output..

Table:parentmaster

ID Name


8571 File/sam
5475 Folder/Pat
6808 path/test
7591 file/test2
4485 Pr/dsn/

Table:Tree

Parent child Parentchildtree


8571 5475 8571-->5475
8571 6808 8571-->6808
8571 7591 8571-->7591
5475 4485 8571-->5475-->4485

Expected output

Table:Treedesc

Parent child Parentchildtree ParentchildtreeDesc


8571 5475 8571-->5475 File/sam-->Folder/Pat
8571 6808 8571-->6808 File/sam-->path/test
8571 7591 8571-->7591 File/sam-->file/test2
5475 4485 8571-->5475-->4485 File/sam-->Folder/Pat-->Pr/dsn/

Thanks for your help in advance


#2

This is the basic idea, though it won't work in your case due to bad table design:

select t.Parent, t.child, t.Parentchildtree, p1.name + '-->' + p2.name
from Tree t
join parentmaster p1
  on t.Parent = p1.id
join parentmaster p2
  on t.child = p2.id

This won't produce the last row because you have a grandparent, parent, child structure but that is only captured in the string '8571-->5475-->4485'. It would be better if you had a separate row to show the 5475 --> 4485 relationship.


#3

Thanks for the immediate response..

Yes..It is not pulling the correct rows for the last row..
Please provide the query to show separate row instead..


#4

You need to change your table design to do this properly


#5

How would be the table design should be.since I have data for the 2 tables already loaded ..
I can move idata to new tables if you suggest the structure..


#6

I think you need a classic tree structure. So each row has:

childid, parentid

then you can traverse the tree with a recursive CTE. check out these links:

https://technet.microsoft.com/en-ca/library/ms186243(v=sql.105).aspx

http://stackoverflow.com/questions/14274942/sql-server-cte-and-recursion-example