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