07-26-2023, 04:40 PM
I try getting all parent categories for each category in select using the following query:
select @c := c.id_category,c.id_parent,
(SELECT
GROUP_CONCAT( @r := (
SELECT c1.id_parent
FROM ps_category c1
WHERE c1.id_category = @r
)) AS parent
FROM (
SELECT @r := @c
) vars,
ps_category c2
where @r<>0) p
from ps_category c
join (select @c:=0) tmp
I expect to get something like this:`
id_category_id; id_parent; p
1; 0; 0
2; 1; 1,0
3; 1; 1,0
4; 2; 2,1,0
...`
but it seems that something not working with initialisation of `@r` with `@c` , what I'm doing wrong?
select @c := c.id_category,c.id_parent,
(SELECT
GROUP_CONCAT( @r := (
SELECT c1.id_parent
FROM ps_category c1
WHERE c1.id_category = @r
)) AS parent
FROM (
SELECT @r := @c
) vars,
ps_category c2
where @r<>0) p
from ps_category c
join (select @c:=0) tmp
I expect to get something like this:`
id_category_id; id_parent; p
1; 0; 0
2; 1; 1,0
3; 1; 1,0
4; 2; 2,1,0
...`
but it seems that something not working with initialisation of `@r` with `@c` , what I'm doing wrong?