CODEWITHSUNDEEP
sqlsql-servert-sqlselect
Seb
Seb

Reputation: 830

tricky select statement

I have a table with categories, each category as an ID, a Name and a ParentID. The problem is that there are 3 levels, parent categories, sub categories and child categories.

I can extract parent categories with a simple SELECT and a WHERE ParentID IS NULL clause as such:

SELECT *
FROM Category
WHERE ParentID IS NULL

However, a WHERE ParentID IS NOT NULL clause will return both, sub categories as well as child categories.

I'm looking for a way to extract only sub categories, and only child categories.

Upvotes: 3

Views: 256

Answers (3)

shunty
shunty

Reputation: 3758

How about something like:

-- Root parents
select c.* from categories c where c.ParentID is null

-- Second level. Select where parentid is a root category.
select c.* from categories c 
where c.ParentID in (select c1.ID from categories c1 where c1.ParentID is null);

-- Third level. Select where parentid is a second level category
with second_level_cats (ID) as (
  select c.ID from categories c 
  where c.ParentID in (select c1.ID from categories c1 where c1.ParentID is null)
  )
select c.* from categories c 
where c.ParentID in (select l2.ID from second_level_cats l2)

May not be entirely optimal but it seems to work. If there's only a relatively low number of rows and you're only ever going to go to three levels then it should suffice.

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79909

Typically, for these sort of problems, it is better to use the Recursive Queries Using Common Table Expressions. Something like so:

;WITH CategoriesTree(CategoryID, CategoryName, ParentName, CategoryLevel)
AS
(
   SELECT 
     c.ID, 
     c.Name, 
     CAST('No Parent' AS VARCHAR(50)) AS ParentName, 
     0 AS CategoryLevel
   FROM @Categories c
   WHERE c.ParentID IS NULL
   UNION ALL
   SELECT c.ID, c.Name, p.CategoryName, p.CategoryLevel + 1
   FROM CategoriesTree p
   INNER JOIN @Categories c ON c.ParentID = p.CategoryID
)
SELECT * 
FROM CategoriesTree
Where CategoryLevel = some id;

SQL Fiddle Demo

This will give you:

CATEGORYID       CATEGORYNAME         PARENTNAME      CATEGORYLEVEL
    1         Root Cateogry         No Parent               0
    2         Sub Cateogry 1        Root Cateogry           1
    3         Sub Cateogry 2        Root Cateogry           1
    4         Sub Cateogry 3        Root Cateogry           1
    8         sub Cateogry 1 of 3   Sub Cateogry 3          2
    7         Sub Cateogry 1 of 2   Sub Cateogry 2          2
    5         Sub Cateogry 1 of 1   Sub Cateogry 1          2
    6         sub Cateogry 2 of 1   Sub Cateogry 1          2

How does this work?

Using this query, you can control what level of categories you want to select. For instance, for the sample data, I used in the previous demo, here is the categories tree:

                        1: RootCategory                Category Level:  0
                               |
                               |
                 ---------------------------- 
                 |             |            |
                 |             |            |
              2: Sub1        3: Sub2      4: sub3      Category Level:   1
                 |             |            |
          ------------         |            |
          |          |         |            |
          |          |         |            |
     5: Sub1of1  6: Sub2of1   7: sub1of2   8: sub1of3  Category Level:   2

This query will give you this categories tree with the new generated CategoryLevel column.

Note that: In the sample data I used in the demo, there was only one parent category (the categories with parentid IS NULL). However, the query will for work fine in case there were a lot of parent categories. And this because of the anchor query of the CTE, which is: 

SELECT 
     c.ID, 
     c.Name, 
     CAST('No Parent' AS VARCHAR(50)) AS ParentName, 
     0 AS CategoryLevel
FROM @Categories c
WHERE c.ParentID IS NULL;

Then, you can use the generated column CategoryLevel to select only the child categories of the level that you are interested in.

For example, if you need to select only the sub categories of the first sub categories of the root category, you can get these categories using the predicate CategoryLevel = 2:

;WITH CategoriesTree(CategoryID, CategoryName, ParentName, CategoryLevel)
AS
(
    ...
)
SELECT * 
FROM CategoriesTree
WHERE CategoryLevel = 2;

This will give you:

CATEGORYID       CATEGORYNAME         PARENTNAME      CATEGORYLEVEL
    8        sub Cateogry 1 of 3    Sub Cateogry 3          2
    7        Sub Cateogry 1 of 2    Sub Cateogry 2          2
    5        Sub Cateogry 1 of 1    Sub Cateogry 1          2
    6        sub Cateogry 2 of 1    Sub Cateogry 1          2

SQL Fiddle Demo

Upvotes: 5

Emil Ivanov
Emil Ivanov

Reputation: 37633

First level categories - you have it:

SELECT *
FROM Category
WHERE ParentID IS NULL

For the second level categories you can try:

SELECT * FROM Category
WHERE ParentID IN (SELECT ID FROM Category WHERE ParentID IS NULL).

For the third:

SELECT * FROM Category
WHERE ParentID IN (SELECT ID FROM Category WHERE ParentID IS NOT NULL)

(Not tested)

Upvotes: 2

Related Questions