Query for treeview

  • 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>

  • 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!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • 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')

  • Ok

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

    How to post your question to get the best and quick help[/url]

  • 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/

  • Yes I understand I'll try being more clear next time.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply