September 11, 2012 at 7:26 pm
I have a query which is working fine.
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.
In other words Child column is directly under Table2's row column name (Father), but occasionally it comes under Table1 with no relation to Table 2.
How can I out put that in a query for a treeview? I am assuming that I will have to program the out come in c# also with 3 for loops and 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 treeview, but I am having a problem building a query in sql. Any help is much appreciated.
The query below shows all Parent, then child then grand child(all well and 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')
)
Asp.Net Telerik Treeview DataBinding Code:
DataMember="Table1" TextField="Assets" ValueField="AssetSysID" ToolTip="Asset" ImageUrl="~/Images/DeleteIco.png"/>
<telerik:RadTreeNodeBinding DataMember="Table2" TextField="Feature" ValueField="ACISysID" ToolTip="Feature" ImageUrl="~/Images/CutIco.png"/>
<telerik:RadTreeNodeBinding DataMember="Table3" TextField="Equipment" ValueField="ITMSysID" ToolTip="Equipment" ImageUrl="~/Images/EditIco.png"/>
</DataBindings>
September 12, 2012 at 3:20 am
Welcome to the forum!
In order to help you we will need a bit more details from you.
Please spend some time to read the following:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
September 17, 2012 at 7:49 am
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')
September 17, 2012 at 7:55 am
September 17, 2012 at 8:07 am
Yep my crystal ball is still out for repairs. We can't see your screen and our ability at mind reading is hindered greatly by reality. There is nothing like posting ddl (create table scripts) and sample data (insert statements) and what you posted is nothing like ddl and sample data. Your request seems like it probably is not too difficult IF there is enough information, which as of now there is no information.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 17, 2012 at 8:21 am
Yes I understand I'll try being more clear next time.
July 1, 2014 at 2:51 am
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply