JOIN cte2 c ON s.ParentCategoryID = c.CategoryID It will have also show how we can use multiple Common Table Extensions in single SQL Query. Query Part 3: Find Parent and Children both of a particular record now I join above both two queries into 1 to get all parents and children records from my database tables. Here “Mobile” is Parent category record with CategoryID 10 and “Smartphones” and “Normal Mobile” is child category records with CategoryId 12 and 15. If you don't want "Mobiles" in your result and only want parents then simply change the condition before "UNION ALL" to not return that (or any) record. This query will produce a result as above. This query will produce a result as follows. JOIN cte c ON s.ChildCategoryID = c.CategoryID Query Part 1: Find only Parents of a particular record The following query is used to get the record and it’s all parent category records from the above tables. (, ) VALUES (11, 14)Īfter inserting above records the data hierarchy will be something like this in database,Ĭlothing > Men's Clothing > Jeans Pent, T-Shirts and Shirts.Ĭlothing > Kid's Clothing > Jeans Pent, T-Shirts and Shirts.Ĭlothing > Women's Clothing > Jeans Pent and Sarees.įor making my explanation cleaner and easy to understand, I distributed my query into 3 parts. IDENTITY(1,1) NOT NULL,ĬONSTRAINT PRIMARY KEY CLUSTERED ( ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON Īfter creating above two tables, I am adding some records for our testing. Following is the script to create two tables with test data. I also added a few records to demonstrate how the solution will work. The two tables will be Categories and CategoryRelation. For my example, I will create two tables to manage Master and relationship entries. For the users, who want to work with hierarchical data relationships, this will work like a dream. It will not only find Parent of children for a particular level but you can use it to find both till N level. In the beginning, this seems to be much complex to develop but the use of Common Table Extension (CTE) and recursion call in SQL makes it too easier for you. 95 of families are on track with their payments, thanks to brightwheel Before, it was really bad. Stay on top of your finances and year-end taxes. Give families the convenience of secure, automatic online payments and never chase a payment again.
#The inn between parents last names how to
Today I am going to explain how to get all parents and children of a particular record using SQL query and Common Table Expression. Easily set up and automate tuition billing and invoices.
0 kommentar(er)
