Query Help

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

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.

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..

You need to change your table design to do this properly

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..

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