Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Query for treeview Expand / Collapse
Author
Message
Posted Tuesday, September 11, 2012 7:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 01, 2012 7:33 AM
Points: 3, Visits: 22
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>
Post #1357757
Posted Wednesday, September 12, 2012 3:20 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
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/


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1357854
Posted Monday, September 17, 2012 7:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 01, 2012 7:33 AM
Points: 3, Visits: 22
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')
Post #1360211
Posted Monday, September 17, 2012 7:55 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
Ok

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1360216
Posted Monday, September 17, 2012 8:07 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:24 PM
Points: 11,990, Visits: 11,007
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1360226
Posted Monday, September 17, 2012 8:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 01, 2012 7:33 AM
Points: 3, Visits: 22
Yes I understand I'll try being more clear next time.
Post #1360237
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse