CREATE TABLE #test (Id int, ParentId Int, Top1 int, Left1 int, Width int, Height int, Color varchar(20)) INSERT INTO #test VALUES(1,0,10,10,100,100,'White') INSERT INTO #test VALUES(2,1,NULL,NULL,200,NULL,NULL) INSERT INTO #test VALUES(3,2,NULL,NULL,NULL,NULL,'Red') INSERT INTO #test VALUES(4,1,20,20,NULL,NULL,'Green') SELECT #test.Id, COALESCE(#test.Top1 , Pr.Top1 ,ISNULL((SELECT Top1 FROM #test WHERE #Test.Id = Pr.ParentId),0)) AS Top1, COALESCE(#test.Left1 , Pr.Left1 ,ISNULL((SELECT Left1 FROM #test WHERE #Test.Id = Pr.ParentId),0)) AS Left1, COALESCE(#test.Width , Pr.Width ,ISNULL((SELECT Width FROM #test WHERE #Test.Id = Pr.ParentId),0)) AS Width, COALESCE(#test.Height, Pr.Height,ISNULL((SELECT Height FROM #test WHERE #Test.Id = Pr.ParentId),0)) AS Height, COALESCE(#test.Color , Pr.Color ,ISNULL((SELECT Color FROM #test WHERE #Test.Id = Pr.ParentId),'')) AS Color FROM #test LEFT JOIN #test Pr ON #test.ParentId = Pr.Id DROP TABLE #testBut will works if you have no more than 2 levels.
>ID ParentID Top Left Width Height Color >3 2 0 0 200 0 Red >>Regards,
>>*** SQL Server? >>SELECT Id, COALESCE(Top , Pr.Top ,0) AS Top, >> COALESCE(Left , Pr.Left ,0) AS Left, >> COALESCE(Width , Pr.Width ,0) AS Width, >> COALESCE(Height, Pr.Height,0) AS Width, >> COALESCE(Color , Pr.Color ,0) AS Color >>FROM MyTable >>LEFT JOIN MyTable Pr ON MyTable.Parent = Pr.Id >>>>
>>>ID ParentID Top Left Width Height Color >>>1 0 10 10 100 100 White >>>2 1 null null 200 null null >>>3 2 null null null null Red >>>4 1 20 20 null null Green >>>* where non-null values override the parent value. This means ID 3 would be: >>> 10 10 200 100 Red >>>* ID 4 would be: >>> 20 20 100 100 Green >>>* etc >>>>>>I had this coded in VFP using recursion to 'walk' back up the tree to the root record and then build the final spec by unwinding back down. Any suggestion on how to implement this using ADO/SQLServer (with as much as possible in SQL since this data may also be accessed by other front ends)?