Unknown column 'parent.ID' in 'having clause' WITH RECURSIVE
CategoryHierarchy AS (
SELECT
c.URL,
c.ID,
c.Abreviacion,
c.Nombre,
c.Padre,
c.CatSat,
c.Sucursal,
1 AS Level,
c.Nombre as Path2,
c.Lft,
c.Status
FROM categoria c
WHERE c.Padre = '' and c.Status = 'ACTI' and c.sucursal = 2747
UNION ALL
SELECT
c.URL,
c.ID,
c.Abreviacion,
c.Nombre,
c.Padre,
c.CatSat,
c.Sucursal,
ch.Level + 1 AS Level,
CONCAT(ch.Path2, ' - ', c.Nombre) AS Path2,
c.Lft,
c.Status
FROM categoria c
INNER JOIN CategoryHierarchy ch ON c.Padre = ch.ID
WHERE c.Sucursal = ch.Sucursal and c.Status = 'ACTI' and c.sucursal = 2747
) SELECT CONCAT(REPEAT('---', Level - 1), Nombre) AS Nombre,
node.ID, node.Url ,(select count(*) from producto_categoria pc, producto p, producto_variante pv where p.id = pv.producto and p.sucursal = pv.sucursal and
pc.id_producto = p.id and pc.sucursal = p.sucursal and pv.ecommerce = 'SI' and pc.id_categoria = node.id and pc.sucursal = node.sucursal and pv.Status in ('ACTI', 'SOLO')) as 'Cant' FROM CategoryHierarchy as node WHERE Sucursal = 2747 HAVING COUNT(parent.ID) < 3 and `Cant` > 0 order by node.lft, node.nombre
Categorías
