SQL query of parent child query is defined in two tables
I know there are similar questions here, but I couldn't find my specific situation in them, so I want to ask you, and I hope you can advise me on the best way to do this.
I have this .
I have two tables: SQL Fiddle
CATEGORIES (ID,
NAME,
PARENT_ID,
SORT_ORDER)
AND
CATEGORY_ITEMS (ID,
NAME,
CATEGORY_ID,
SORT_ORDER)
These two tables are somehow a summary of my actual database schema (I have no way of changing it). In my actual database schema, these categories can define sets that are valid based on certain datetime values. I would like to know if it is possible to return results as shown below with a single query.
| NAME |
|-------------------|
|Category 1 |
|Item 1 Category 1 |
|Item 2 Category 1 |
|Category 1.1 |
|Item 1 Category 1.1|
|Item 2 Category 1.1|
|Category 1.2 |
| Category 1.2.1 |
| Category 2 |
|Item 1 Category 2 |
|Item 2 Category 2 |
| Category 2.1 |
| Category 2.2 |
| Category 3 |
| Category 3.1 |
| Category 3.2 |
In the SQL Fiddle, you can see my work so far. I think I can get what I want by getting the tree structure from CATEGORIES
into the cursor, and for each record, I have to get its records from CATEGORY_ITEMS
, but I was wondering if there is a way to do this without a cursor, I admit that I am not good at I'm good at SQL, but I'm trying to be better, so I'm asking this question.
source to share
Try the following:
;WITH CTE(ID, NAME, PARENT_ID, SORT_ORDER, [Level], ord) AS (
SELECT c.ID, CONVERT(nvarchar(100), c.NAME),
c.PARENT_ID, c.SORT_ORDER, 1 [level],
CONVERT(nvarchar(255), RIGHT('0000' + CONVERT(nvarchar(255), c.ID), 4)) AS ord
FROM CATEGORIES c
WHERE c.PARENT_ID IS NULL
UNION ALL
SELECT c.ID, CONVERT(nvarchar(100), REPLICATE(' ', [Level]) + c.NAME),
c.PARENT_ID, c.SORT_ORDER, CTE.[Level] + 1,
CONVERT(nvarchar(255),RIGHT('0000' + CONVERT(nvarchar(255), CTE.ID), 4) + RIGHT('0000' + CONVERT(nvarchar(255), c.ID), 4)) AS ord
FROM CATEGORIES c
JOIN CTE ON c.PARENT_ID = CTE.ID
WHERE c.PARENT_ID IS NOT NULL)
SELECT NAME
FROM (
SELECT NAME, ord, 1 As ord2
FROM CTE
UNION ALL
SELECT ci.NAME, c.ord, 2 + ci.SORT_ORDER
FROM CATEGORY_ITEMS ci
JOIN CTE c ON ci.CATEGORY_ID = c.ID) dt
ORDER BY ord, ord2
For this:
NAME
Category 1
Item 1 Category 1
Item 2 Category 1
Category 1.1
Item 1 Category 1.1
Item 2 Category 1.1
Category 1.2
Category 1.2.1
Category 2
Item 1 Category 2
Item 2 Category 2
Category 2.1
Category 2.2
Category 3
Category 3.1
Category 3.2
source to share