sql query parent child

If  we need to display the child along with their parents in front of them , if there is no parent we need to display no parent or null.

Or another scenario is the most asked question in interviews is display employee names along with their manager names in front of the employees

My table is having same parentid column in category table

CategoryID

ParentCategoryID

Name

It does not matter if you have separate table.

Here is the query

SELECT C.[CategoryID]
,C.[ParentCategoryID]
,C.[Name]
,(Case When C.ParentCategoryID <> 0 Then (select Name From Category Where CategoryID = C.ParentCategoryID) else ‘No Parent’ End) As [Parent]
FROM [Category] C

Another way to achieve this is

SELECT C.[CategoryID]

,C.[Name]
,C.[ParentCategoryID]
, (Select Name From Category Where CategoryID In (C.ParentCategoryID)) As ‘Parent’
FROM [MetroShirts].[dbo].[Category] C

 

 

Parent Child SQL Query
Parent Child SQL Query

Here is another version when parent child relation is in two tables

Select PC.ProductCategoryID , PSC.ProductSubcategoryID , PSC.Name , PC.Name as [Parent] from AdventureWorks2008.Production.ProductCategory PC
Inner join AdventureWorks2008.Production.ProductSubcategory PSC
ON PC.ProductCategoryID = PSC.ProductCategoryID

SQL query parent child relation ship
SQL query parent child relation ship

SQL To XML

Certain times we need to export our grid data or our custom query data to xml format.
.Net provides a very easy way to that , The two data object i.e. Dataset and DataTable has built-in overloads to do this.

public void WriteSQLToXML()
        {
 using (SqlConnection con = new SqlConnection(“server=your_sql_server_name;Database=your_db_name;Integrated Security=true;”))
            {
                if (con.State == ConnectionState.Closed)
                {
                    con.Open();
                }
using (SqlCommand cm = new SqlCommand(“Select * from your_table_name” , con))
                {
using (SqlDataAdapter DA = new SqlDataAdapter(cm))
                    {
                        DataSet DS = new DataSet(“mytable”);
                        DA.Fill(DS, “mytable”);


    //for winforms
   string _path = Application.StartupPath + “myData.xml”;


   //for webform
   string _path = Server.MapPath(“”) + “\\myData.xml”;


//it will save myData.xml to your local root directory of your application
                       
DS.WriteXml(_path);
                    }
                }
                
            }
        }