I have parent records in two tables,
Table2. Some records in
Table3 are children of
Table1, and some are children of
I would like to build a query that I can use to populate a TreeView control with the data from
Table3 displaying either under
Table1 if it only has a parent in
Table1 and not
Table2 if it has parent records in both
Is it possible that if the table3's column(Child) is only related to table 1 to show it under table 1 and not under table 2, but at the same time another (Child) has a parent in table 2 (which usually is the case) it will show under table 2 as its currently doing.
How can I output that in a query for a TreeView? I am assuming that I will have to program the output in C#, with 3
FOR loops. In the second loop I can check if the column is grandchild or Child and make that as a second row or 2nd node of the TreeView, but I am having a problem building a query in SQL.
The query below shows all Parent, then child then grandchild, which is working, but what is desired is at times child takes place of a father.
DECLARE @x AS XML SET @x = ( SELECT DISTINCT Table1.AssetSysID , Table1.Asset_ID , Table1.FromLR , Table1.Asset_ID + ', ' + Table1.[Desc2] AS GarndFather , Table2.ACISysID , Table2.PAssetSysID , Table2.FeatureName + ', ' + Table2.[DESC] AS Father , Table3.ITMSysID , Table3.Item_ID + ',' + Table3.[DESC] AS Child FROM Table1 LEFT OUTER JOIN Table2 ON Table1.AssetSysID = Table2.PAssetSysID LEFT OUTER JOIN Table3 ON Table1.AssetSysID = Table3.AssetSysID AND Table2.ACISysID = Table3.ACISysID WHERE (Table1.AssetType = @AssetType) FOR XML AUTO,root('xml') );
I've modified the code above to reflect some changes:
DECLARE @x AS XML SET @x = ( SELECT Table1.AssetObjID AS "@AssetObjID" , Table1.Asset_ID AS "@Asset_ID" , Table1.FromLR AS "@FromLR" , Table1.AssetType + ', ' + Table1.StreetName + ', ' + Table1.FromMunicNo AS "@FirstRow" , ( SELECT Table2.ACIObjID AS "@ACIObjID" , Table2.PAssetObjID AS "@PAssetObjID" , Table2.Feature_ID + ', ' + Table2.FeatureName AS "@ChildOfFirstRow" , ( SELECT Table3.ITMObjID AS "@ITMObjID" , Table3.Item_ID + ',' + Table3.[DESC] AS "@GrandChildOfFirstRow" FROM Table3 WHERE Table1.AssetObjID = Table3.AssetObjID AND Table2.ACIObjID = Table3.ACIObjID FOR XML PATH('Table3'), TYPE ) FROM Table2 WHERE Table1.AssetObjID = Table2.PAssetObjID FOR XML PATH('Table2'), TYPE ) , ( SELECT Table3.ITMObjID AS "@ITMObjID" , Table3.Item_ID + ',' + Table3.[DESC] AS "@GrandChildOfFirstRow" FROM Table3 WHERE Table1.AssetObjID = Table3.AssetObjID AND Table2.ACIObjID <> Table3.ACIObjID FOR XML PATH('Table3'), TYPE ) FROM Table1 WHERE Table1.AssetType = 'xxxx' FOR XML PATH('Table1'), root('xml') );