bookmarker bookmarker - 2 months ago 15
MySQL Question

Hierarchical SQL Query WITH parent child items

Category (id, name, parent_id);
1 Electronics NULL
2 Computers 1
3 Notebooks 2
4 Desktops 2

Products (id, name, category_id);
1 NotebookX 3
2 NotebookY 3
3 NotebookZ 3
4 DesktopX 2
5 DesktopY 2


1- I want to select parent category list of a specified category.

2- I want to select product list of a category ans sub categories.

Select category 1 should return 5 products.
1 NotebookX 3
2 NotebookY 3
3 NotebookZ 3
4 DesktopX 2
5 DesktopY 2
Select category 2 should return 5 products.
1 NotebookX 3
2 NotebookY 3
3 NotebookZ 3
4 DesktopX 2
5 DesktopY 2
Select category 3 should return 3 products.
1 NotebookX 3
2 NotebookY 3
3 NotebookZ 3
Select category 4 should return 2 products.
4 DesktopX 2
5 DesktopY 2


I am new at sql recursive queries so could not create.

WITH RECURSIVE graph AS (
SELECT id, name
FROM category
WHERE parent_id IS NULL
UNION ALL
SELECT v.id, v.name
FROM category as v
JOIN graph r on v.parent_id = r.id
????????????
????????????
)
SELECT id, name FROM graph;


(I am using postgreql. But you can write any database.)

Answer

Give it a try (SQL Server solution):

DECLARE @category int = 3

;WITH rec AS (
SELECT *
FROM Category c
WHERE c.id = @category
UNION ALL
SELECT c.*
FROM rec r
INNER JOIN Category c
    ON c.parent_id = r.id
)

SELECT p.*
FROM Products p
INNER JOIN rec r
    ON r.id = p.category_id

At first we get a category we choose. Than we select from Category all its children in a recursive part. After that we got in rec all sub categories. Than we join Products with categories from rec.

EDIT#1

You can put rec results into temp table and then use them:

SELECT *
INTO #rec
FROM rec

SELECT p.*
FROM Products p
INNER JOIN #rec r
    ON r.id = p.category_id

SELECT *
FROM #rec

DROP TABLE #rec

Output for @category=3:

id          name      category_id
----------- --------- -----------
1           NotebookX 3
2           NotebookY 3
3           NotebookZ 3

(3 row(s) affected)

id          name        parent_id
----------- ----------- -----------
3           Notebooks   2

(1 row(s) affected)

EDIT#2

To get descendants:

DECLARE @category int = 2

;WITH rec AS (
SELECT *
FROM Category c
WHERE c.id = @category
UNION ALL
SELECT c.*
FROM rec r
INNER JOIN Category c
    ON c.id = r.parent_id
)

SELECT *
FROM rec

Output:

id  name        parent_id
2   Computers   1
1   Electronics NULL

For parents:

DECLARE @category int = 2

;WITH rec AS (
SELECT *
FROM Category c
WHERE c.id = @category
UNION ALL
SELECT c.*
FROM rec r
INNER JOIN Category c
    ON c.parent_id = r.id
)

Output:

id  name        parent_id
2   Computers   1
3   Notebooks   2
4   Desktops    2