i have this table named products:
Create Table Products(
ProductId Int,
ParentId Int,
)
Insert Products
Select 2, 3
Union All
Select 1, 2
Union All
Select 3, 4
Union All
Select 4, 5
Union All
Select 6, 5
Union All
Select 8, 7
Union All
Select 10, 9
Union All
Select 12, 11
Union All
Select 13, 6
And then i run this Cte Query:
;WITH CTE AS
(SELECT ProductId AS ROOTID, ParentId AS ProductId
FROM Products
WHERE ProductId=1
UNION ALL
SELECT c.ROOTID, f.ParentId
FROM Products f
INNER JOIN CTE c ON f.ProductId = c.ProductId)
SELECT ROOTID, productId FROM CTE
WHERE ProductId <> 1;
The query give me this:
| ROOTID | ProductId |
|---|---|
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 1 | 5 |
But i need to have:
| ROOTID | ProductId |
|---|---|
| 1 | 2, 3, 4, 5 |
Or if i call the id 13 i expect to have:
| ROOTID | ProductId |
|---|---|
| 13 | 6, 5 |
You can see the code running in the link below: http://sqlfiddle.com/#!18/615c3/77
How i can have my results but not using Cte but converting in MySQL 5 .7
