I've never been able to grasp the usage of JOIN when querying the database, and would like some help building the correct SQL query for my task.

My database table looks like this:

As you can see this table uses internal foreign key linking (not sure what the correct term is). I am trying to write a query that will JOIN the correct columns and their values (...and then have it set using "for xml auto".  My expected generated xml should look like this (note that I am not outputting the XML because it will be used as a datasource, but this is for clarity):


<Categories>

    <Category ID="1" Name="Fruits" checked="false">
        <SubCategory Id="4" Name="Apples" checked="false">
            <SubSubCategory Id="16" Name="Granny Smith" checked="false" />
            <SubSubCategory Id="17" Name="Golden Delicious" checked="false" />
        </SubCategory>
        <SubCategory Id="5" Name="Grapes" checked="false" />
        <SubCategory Id="6" Name="Bananas" checked="false" />
        <SubCategory Id="7" Name="Peachs" checked="false" />
    </Category>

    <Category ID="2" Name="Colours" checked="false">
        <SubCategory Id="8" Name="Red" checked="false" />
        <SubCategory Id="9" Name="Blue" checked="false" />
        <SubCategory Id="10" Name="Green" checked="false" />
            <SubSubCategory Id="18" Name="Lime Green" checked="false">
            <SubSubCategory Id="19" Name="Dark Green" checked="false">
            <SubSubCategory Id="20" Name="Light Green" checked="false">
        </SubCategory>
    </Category>

    <Category ID="3" Name="Sizes" checked="false">
        <SubCategory Id="11" Name="X-Small" checked="false" />
        <SubCategory Id="12" Name="Small" checked="false" />
        <SubCategory Id="13 Name="Medium" checked="false" />
        <SubCategory Id="14" Name="Large" checked="false" />
        <SubCategory Id="15" Name="X-Large" checked="false" />
    </Category>

</Categories>

Can anyone help me with this query? 

Hello,

 I would say first thing is you need two tables. One table called for example ProductTypes (ie Fruits, Colors, Size) then another table called maybe ProductItems(ie Apples, Blue, X-Large). Make sense. This is called normalizing. Then For ProductTypes you would assign a Primary Key and on Product ITems you would have a Foreign Key that references ProductTypesId. So it owul dbe something like this:

ProductTypes:

ProductTypeId      ProductType

1                         Fruit

2                         Colors

3                         Size

ProductItems:

ProductItemId         ProducTypeId                             ProductItem

1                            1(References ProductTypeId)       Apple

2                            1                                               Banana

3                            1                                               Orange

4                            3                                               Small

5                            2                                               Blue

etc                         etc                                             etc

So lets say you want to return a query of nothing but fruit.

 You would do something like this:
SELECT pi.ProductItem FROM ProductItem pi INNER JOIN ProductType pt ON pt.ProductTypeId = pi.ProductTypeId WHERE pt.ProducTypeId = @ProdcutTypeId FOR XML AUTO

That would return you all fruits. Assuming you passed in the the ProductTypeId of fruits :-)

Make sense?

For your requirement you have to use FOR XML Explicit.

Following is the sample query similar to your requirement.

SELECT 1                    as Tag,
         NULL                 as Parent,
         Customers.CustomerID as [Customer!1!CustomerID],
         NULL                 as [Order!2!OrderID]
FROM Customers

UNION ALL
SELECT 2,
         1,
         Customers.CustomerID,
         Orders.OrderID
FROM Customers, Orders
WHERE Customers.CustomerID = Orders.CustomerID
ORDER BY [Customer!1!CustomerID], [Order!2!OrderID]
FOR XML EXPLICIT

@ksridharbabuus...
Thanks for your quick reply.  That looks like such a complex query I cant even begin to work it out.  I tried using "common sense" to rewrite it to suit my needs, but for someone who can barley grasp the concept of a basic JOIN, I am totally lost when looking at your example query.

What I do notice is that your example still uses 2 tables 'Customers' and 'Orders' (i may be wrong so accept my appologies).  My data is all in 1 table (as per my example above).

Would I be asking too much if i asked you to help me rewrite it exactly to suit my needs?

For a more directly related example to the one im trying to achive, lets use the following as the data in my tables:

Table Name: dbo.MusicGenres

I've progressed,  I think I have got the query 99% correct.  This is what i have come up with so far (i call GetXmlDate() from my Page_Load at the moment):

                                          "NULL as Parent, " +

                                          "NULL as [TheParent!2!ParentID] " +

                           "UNION ALL " +

                           "SELECT 2, " +

                                         "MusicGenres.GenreID, " +

                            "FROM MusicGenres " +

                            "ORDER BY [TheGenre!1!GenreID], [TheParent!2!ParentID] " +

                            "FOR XML EXPLICIT";

    SqlConnection objConn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ToString());

    //todo: need to return xr somehow

    //i cant figure out how to return the xml out of this fuction yet

Check if this article helps you:

http://www.theserverside.net/tt/articles/showarticle.tss?id=HeirarchicalQueries

Please let us know if it not solves your problem.

It feels like im so close. I've looked at that article and it throws me off track.  ksridharbabuus, you have been extreamly helpful so far, dont throw a link now please. lol

Appologies, but I need to bump this *bump*

 Below query will fetch the XML o/p for 2 level of sub categorization.  Try and put some extra blocks yourself  to further enhance your query for your needs.

select 
	1 as tag ,
	null as parent ,
	c.tableid as [Category!1!Id] ,
	c.item as [Category!1!Name] ,
	null as [SubCategory!2!Id] ,
	null as [SubCategory!2!Name]
from categories c
where isnull ( c.parentid , '' ) = ''
union all
select
	2 ,
	1 ,
	c1.tableid ,
	c1.item ,
	c2.tableid ,
	c2.item
from categories c1 , categories c2
where c2.parentid = c1.tableid
and isnull ( c1.parentid , '' ) = ''
order by [Category!1!Id] , [SubCategory!2!Id]
for xml explicit

Thanks for that helpful reply.  I can almost understand how these queries are working, however I am still not getting the expected results.

My code looks like this (and I am using dhimant's query - slightly changed to suit my table)...

private void GetXmlData()
    {
        string strSQL = "select " +
                            "1 as tag, " +
                            "null as parent, " +
                            "c.GenreID as [Category!1!Id], " +
                            "c.GenreName as [Category!1!Name], " +
                            "null as [SubCategory!2!Id], " +
                            "null as [SubCategory!2!Name] " +
                        "from MusicGenres c " +
                        "where isnull ( c.ParentID , '' ) = '' " +

                        "union all " +

                        "select " +
                            "2, " +
                            "1, " +
                            "c1.GenreID, " +
                            "c1.GenreName, " +
                            "c2.GenreID, " +
                            "c2.GenreName " +
                        "from MusicGenres c1, MusicGenres c2 " +
                        "where c2.ParentID = c1.GenreID " +
                        "and isnull ( c1.ParentID , '' ) = '' " +
                        "order by [Category!1!Id] , [SubCategory!2!Id] " +
                        "for xml explicit";

        
        SqlConnection objConn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ToString());
        SqlCommand objCommand = new SqlCommand(strSQL, objConn);
        objConn.Open();

        System.Xml.XmlReader xr = objCommand.ExecuteXmlReader();
        int mycount = 1;
        while (xr.Read())
        {
            Response.Write("&lt;b>My Counter: " + mycount + "&lt;/b><br>");
            Response.Write("GenreID: " + xr["Id"] + "&lt;br>");
            Response.Write("GenreName: " + xr["Name"]);
            Response.Write("&lt;hr />");
            mycount++;
        }

        xr.Close();
        objCommand.Dispose();
        objConn.Dispose();
    }

The output looks like this:

My Counter: 1
GenreID: 1
GenreName: All Music

My Counter: 2
GenreID: 2
GenreName: All Commercial

My Counter: 3
GenreID: 3
GenreName: All House

My Counter: 4
GenreID: 4
GenreName: All Hard Dance

My Counter: 5
GenreID: 5
GenreName: All Alternative Dance

My Counter: 6
GenreID: 6
GenreName: All Techno

My Counter: 7
GenreID: 7
GenreName: All Drum and Bass

My Counter: 8
GenreID: 8
GenreName: All Urban

My Counter: 9
GenreID: 9
GenreName: All Alternative Electronic

My Counter: 10
GenreID: 10
GenreName: All Retro

My Counter: 11
GenreID: 11
GenreName: All Rock

My Counter: 12
GenreID:
GenreName:

If you look at my table (in a few posts above), you will see that it's not giving me all the records, only the results where ParentID is Null or 1, but what about the rest?

Any ideas?

In the query there is a where condition to get the records where ParentID is null. Please remove the following condition in the where clause in the above query:

                and isnull ( c1.ParentID , '' ) = ''

Finally query will be as follows:

string strSQL = "select " +
                            "1 as tag, " +
                            "null as parent, " +
                            "c.GenreID as [Category!1!Id], " +
                            "c.GenreName as [Category!1!Name], " +
                            "null as [SubCategory!2!Id], " +
                            "null as [SubCategory!2!Name] " +
                        "from MusicGenres c " +
                        "where isnull ( c.ParentID , '' ) = '' " +

                        "union all " +

                        "select " +
                            "2, " +
                            "1, " +
                            "c1.GenreID, " +
                            "c1.GenreName, " +
                            "c2.GenreID, " +
                            "c2.GenreName " +
                        "from MusicGenres c1, MusicGenres c2 " +
                        "where c2.ParentID = c1.GenreID " +
                        "order by [Category!1!Id] , [SubCategory!2!Id] " +
                        "for xml explicit";

Excellent, Thank you Sri, I am now another step closer.

My output now shows all the fields like this...

My Counter: 1
GenreID: 1
GenreName: All Music

My Counter: 2
GenreID: 2
GenreName: All Commercial

My Counter: 3
GenreID: 3
GenreName: All House

My Counter: 4
GenreID: 4
GenreName: All Hard Dance

My Counter: 5
GenreID: 5
GenreName: All Alternative Dance

My Counter: 6
GenreID: 6
GenreName: All Techno

My Counter: 7
GenreID: 7
GenreName: All Drum and Bass

My Counter: 8
GenreID: 8
GenreName: All Urban

My Counter: 9
GenreID: 9
GenreName: All Alternative Electronic

My Counter: 10
GenreID: 10
GenreName: All Retro

My Counter: 11
GenreID: 11
GenreName: All Rock

My Counter: 12
GenreID: 12
GenreName: Commercial Pop

My Counter: 13
GenreID: 13
GenreName: Commercial Dance

My Counter: 14
GenreID: 14
GenreName: Club Classics

My Counter: 15
GenreID: 15
GenreName: Funky House

...and so on (there are 52 records)...

I think that with the query working correctly, I should be able to populate my TreeView correctly, but im not quite sure how, I have modified my code so that it now looks like this...

protected void Page_Load(object sender, EventArgs e)
    {
        XmlDataSource1.Data = GetXmlData();  //populate XmlDataSource1 with the XML data
    }

    private void GetXmlData()  
    {
        string strSQL = "select " +
                            "1 as tag, " +
                            "null as parent, " +
                            "c.GenreID as [Category!1!Id], " +
                            "c.GenreName as [Category!1!Name], " +
                            "null as [SubCategory!2!Id], " +
                            "null as [SubCategory!2!Name] " +
                        "from MusicGenres c " +
                        "where isnull ( c.ParentID , '' ) = '' " +

                        "union all " +

                        "select " +
                            "2, " +
                            "1, " +
                            "c1.GenreID, " +
                            "c1.GenreName, " +
                            "c2.GenreID, " +
                            "c2.GenreName " +
                        "from MusicGenres c1, MusicGenres c2 " +
                        "where c2.ParentID = c1.GenreID " +
                        "order by [Category!1!Id] , [SubCategory!2!Id] " +
                        "for xml explicit";

        
        SqlConnection objConn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ToString());
        SqlCommand objCommand = new SqlCommand(strSQL, objConn);
        objConn.Open();

        //return ???? //return the query as a XML datsource?
    }

My first question is, will I be able to acheive this.  If so, can someone help me get the code working correctly?

My first problem is that i know "private void GetXmlData()" is the wrong opening line for the method, but im not sure what to use,  and secondly, how do I return the objCommand correctly so that XMLDataSource1.Data is populated correctly?

[ For reference, i am trying to follow Lakshmi's artical on Binding XML Data to TreeView with checked state of checkboxes (http://lakshmik.blogspot.com/2006/04/aspnet-binding-xml-data-to-treeview.html) ]

 Well, I thought you would've solved this yourself.  Anyways, if you haven't done this yet then below is the full implementation code for your example.  Try expanding this as per your needs.

Below is the ASP.Net Page code:

<asp:XmlDataSource ID="MyXMLData" runat="server" XPath="/Categories/*"></asp:XmlDataSource>
            <asp:TreeView ID="MyTreeView" runat="server" ExpandDepth="1" ImageSet="Arrows">
                <ParentNodeStyle Font-Bold="False" />
                <HoverNodeStyle Font-Underline="True" ForeColor="#5555DD" />
                <SelectedNodeStyle Font-Underline="True" HorizontalPadding="0px"
                    VerticalPadding="0px" ForeColor="#5555DD" />
                <NodeStyle Font-Names="Verdana" Font-Size="8pt" ForeColor="Black" HorizontalPadding="5px"
                    NodeSpacing="0px" VerticalPadding="0px" />
                <DataBindings>
                    <asp:TreeNodeBinding DataMember="Category" ValueField="Name" SelectAction="None"
                        TextField="Name" />
                    <asp:TreeNodeBinding DataMember="SubCategory" SelectAction="None" TextField="Name" />
                    <asp:TreeNodeBinding DataMember="SubSubCategory" SelectAction="None" TextField="Name" />
                </DataBindings>
            </asp:TreeView>

 
Below is the Code behind snippet.

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            loadData();
    }

    private void loadData()
    {
        SqlConnection tcon = new SqlConnection(ConfigurationManager.ConnectionStrings["<config connection string>"].ConnectionString.ToString());
        tcon.Open();
        string query = string.Empty;

        query = "select " +
            " 1 as tag ," +
            " null as parent ," +
            " c.tableid as [Category!1!Id] ," +
            " c.item as [Category!1!Name] ," +
            " null as [SubCategory!2!Id] ," +
            " null as [SubCategory!2!Name] ," +
            " null as [SubSubCategory!3!Id] ," +
            " null as [SubSubCategory!3!Name]" +
        " from categories c" +
        " where isnull ( c.parentid , '' ) = ''" +
        " union all" +
        " select" +
            " 2 ," +
            " 1 ," +
            " c1.tableid ," +
            " c1.item ," +
            " c2.tableid ," +
            " c2.item ," +
            " null ," +
            " null" +
        " from categories c1 , categories c2" +
        " where c2.parentid = c1.tableid" +
        " and isnull ( c1.parentid , '' ) = ''" +
        " union all" +
        " select" +
            " 3 ," +
            " 2 ," +
            " c1.parentId ," +
            " c1.item ," +
            " c1.tableid ," +
            " c1.item ," +
            " c2.tableid ," +
            " c2.item" +
        " from categories c1 , categories c2" +
        " where c2.parentid = c1.tableid" +
        " and isnull ( c1.parentid , '' ) != ''" +
        " order by [Category!1!Id] , [SubCategory!2!Id] , [SubSubCategory!3!Id]" +
        " FOR XML EXPLICIT";

        SqlCommand tcom = new SqlCommand(query, tcon);
        string XMLResult = string.Empty;
        XMLResult = "<Categories>" + tcom.ExecuteScalar().ToString() + "</Categories>";
        tcom.Dispose();
        tcon.Dispose();
        MyXMLData.Data = XMLResult;
        MyTreeView.DataSource = MyXMLData;
        MyTreeView.DataBind();
    }

 

 Note that this query is for 3 level of child nodes as I was following the same table which you posted in your first post and not the MusicGenres table which you posted later on, and that is why I included the and isnull ( c1.parentid , '' ) = '' in my earlier post.  You'll have to modify the query for 2 level of sub-categorization which I think now you should be able to do at your own.

I hope now all your problems are solved and this will make your page working.

Thanks dhimant,

This looks exactly like what im trying to acheive.  For clarity sake, I decided to copy your example exactly as it is, using my databse (ie, substituting the table column names and table name respectiveley).

The problem it that when I try to execute the code, I get an error message saying:

but if i change the string to look like this...

XMLResult = "<Categories>" + tcom.ExecuteScalar().ToString() + "&lt;/Categories>";

or this...

<asp:XmlDataSource ID="MyXMLData" runat="server" XPath="/MusicGenres/*"></asp:XmlDataSource>
        
        <asp:TreeView ID="MyTreeView" runat="server" ExpandDepth="1" ImageSet="Arrows">
            <ParentNodeStyle Font-Bold="False" />
            <HoverNodeStyle Font-Underline="True" ForeColor="#5555DD" />
            <SelectedNodeStyle Font-Underline="True" HorizontalPadding="0px" VerticalPadding="0px"
                ForeColor="#5555DD" />
            <NodeStyle Font-Names="Verdana" Font-Size="8pt" ForeColor="Black" HorizontalPadding="5px"
                NodeSpacing="0px" VerticalPadding="0px" />
            <DataBindings>
                <asp:TreeNodeBinding DataMember="Category" ValueField="Name" SelectAction="None" TextField="Name" />
                <asp:TreeNodeBinding DataMember="SubCategory" SelectAction="None" TextField="Name" />
                <asp:TreeNodeBinding DataMember="SubSubCategory" SelectAction="None" TextField="Name" />
            </DataBindings>
        </asp:TreeView>

protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            loadData();
    }

    private void loadData()
    {
        SqlConnection tcon = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ToString());
        tcon.Open();
        string query = string.Empty;

        query = "select " +
            " 1 as tag ," +
            " null as parent ," +
            " c.GenreID as [Category!1!Id] ," +
            " c.GenreName as [Category!1!Name] ," +
            " null as [SubCategory!2!Id] ," +
            " null as [SubCategory!2!Name] ," +
            " null as [SubSubCategory!3!Id] ," +
            " null as [SubSubCategory!3!Name]" +
        " from MusicGenres c" +
        " where isnull ( c.ParentID , '' ) = ''" +
        " union all" +
        " select" +
            " 2 ," +
            " 1 ," +
            " c1.GenreID ," +
            " c1.GenreName ," +
            " c2.GenreID ," +
            " c2.GenreName ," +
            " null ," +
            " null" +
        " from MusicGenres c1 , MusicGenres c2" +
        " where c2.ParentID = c1.GenreID" +
        " and isnull ( c1.ParentID , '' ) = ''" +
        " union all" +
        " select" +
            " 3 ," +
            " 2 ," +
            " c1.ParentID ," +
            " c1.GenreName ," +
            " c1.GenreID ," +
            " c1.GenreName ," +
            " c2.GenreID ," +
            " c2.GenreName" +
        " from MusicGenres c1 , MusicGenres c2" +
        " where c2.ParentID = c1.GenreID" +
        " and isnull ( c1.ParentID , '' ) != ''" +
        " order by [Category!1!Id] , [SubCategory!2!Id] , [SubSubCategory!3!Id]" +
        " FOR XML EXPLICIT";

        SqlCommand tcom = new SqlCommand(query, tcon);
        string XMLResult = string.Empty;
        XMLResult = "<Categories>" + tcom.ExecuteScalar().ToString() + "</Categories>";
        tcom.Dispose();
        tcon.Dispose();
        MyXMLData.Data = XMLResult;
        MyTreeView.DataSource = MyXMLData;
        MyTreeView.DataBind();
    }

Can you please try with the following modified code (I will ExecuteXmlReader instead of ExecuteScalar):

private static void loadData()
        {
            SqlConnection tcon = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ToString());
            tcon.Open();
            string query = string.Empty;

            query = "select " +
                " 1 as tag ," +
                " null as parent ," +
                " c.GenreID as [Category!1!Id] ," +
                " c.GenreName as [Category!1!Name] ," +
                " null as [SubCategory!2!Id] ," +
                " null as [SubCategory!2!Name] ," +
                " null as [SubSubCategory!3!Id] ," +
                " null as [SubSubCategory!3!Name]" +
            " from MusicGenres c" +
            " where isnull ( c.ParentID , '' ) = ''" +
            " union all" +
            " select" +
                " 2 ," +
                " 1 ," +
                " c1.GenreID ," +
                " c1.GenreName ," +
                " c2.GenreID ," +
                " c2.GenreName ," +
                " null ," +
                " null" +
            " from MusicGenres c1 , MusicGenres c2" +
            " where c2.ParentID = c1.GenreID" +
            " and isnull ( c1.ParentID , '' ) = ''" +
            " union all" +
            " select" +
                " 3 ," +
                " 2 ," +
                " c1.ParentID ," +
                " c1.GenreName ," +
                " c1.GenreID ," +
                " c1.GenreName ," +
                " c2.GenreID ," +
                " c2.GenreName" +
            " from MusicGenres c1 , MusicGenres c2" +
            " where c2.ParentID = c1.GenreID" +
            " and isnull ( c1.ParentID , '' ) != ''" +
            " order by [Category!1!Id] , [SubCategory!2!Id] , [SubSubCategory!3!Id]" +
            " FOR XML EXPLICIT";

            SqlCommand tcom = new SqlCommand(query, tcon);
            //string XMLResult = string.Empty;
            StringBuilder XMLResult = new StringBuilder();
            XMLResult.Append("<Categories>");
           
            //XMLResult = "&lt;Categories>" + tcom.ExecuteScalar().ToString() + "</Categories>";
            XmlReader rdr;
            rdr = tcom.ExecuteXmlReader();
            rdr.MoveToContent();
            XMLResult.Append(rdr.ReadInnerXml());
            rdr.Close();
           
            XMLResult.Append("</Categories>");


            tcom.Dispose();
            tcon.Dispose();
            MyXMLData.Data = XMLResult;
            MyTreeView.DataSource = MyXMLData;
            MyTreeView.DataBind();
        }

 Please note that I used ExecuteXmlReader to get the complete xml from the DB Query.

Don't forget to add System.Xml and System.Text Namespaces.