SELECT relacion recursiva
 

Hola amigos.

Tengo un pequeño problema que no se como resolver.

Tengo una entidad llamada "categorias" de productos que puede tener como padre a otra categoria. Mi implementacion es hasta maximo 1 padre. Estos son los campo.

cat_id
cat_nombre
cat_id_padre (puede ser otra cat_id o NULL)
cat_activa

y lo que necesito es poder hacer un SELECT que me traiga el listado de todas las categorias con sus nombres de padres y pruebo algo asi pero no me funciona bien.

//-- esta me trae como quiero pero solo las categorias que tienen padre y no las que no tienen
SELECT cat.cat_id, cat.cat_nombre, cat.cat_id_padre, padre.cat_nombre, cat.cat_activa
FROM categorias cat
JOIN categorias padre ON padre.cat_id = cat.cat_id_padre
ORDER BY cat.cat_nombre

//-- Esta me trae los campos dupliados.
SELECT cat.cat_id, cat.cat_nombre, cat.cat_id_padre, padre.cat_nombre, cat.cat_activa
FROM categorias cat, categorias padre
WHERE padre.cat_id = cat.cat_id_padre
OR cat.cat_id_padre IS NULL
ORDER BY cat.cat_nombre

Me pueden dar una mano. O lo tengo que hacer tipo con un cursor si o si?
A continuacion les doy la sentencia SQL para crear la tabla con datos de ejemplo


SET FOREIGN_KEY_CHECKS=0;

CREATE TABLE `categorias` (
`cat_id` int(3) unsigned NOT NULL auto_increment,
`cat_nombre` varchar(50) NOT NULL default '',
`cat_id_padre` int(3) unsigned default NULL,
`cat_activa` char(1) NOT NULL default '',
PRIMARY KEY (`cat_id`),
UNIQUE KEY `UC_cat_nombre` (`cat_nombre`)
) TYPE=InnoDB COMMENT='Categorias de productos' AUTO_INCREMENT=106 ;


INSERT INTO `categorias` VALUES (1, 'Revisar', NULL, 'N');
INSERT INTO `categorias` VALUES (24, 'Canastas', NULL, 'S');
INSERT INTO `categorias` VALUES (27, 'Aniversarios', NULL, 'S');
INSERT INTO `categorias` VALUES (28, 'Nacimientos', NULL, 'S');
INSERT INTO `categorias` VALUES (29, 'Regalos', NULL, 'S');
INSERT INTO `categorias` VALUES (32, 'Relojes', NULL, 'N');
INSERT INTO `categorias` VALUES (34, 'Bicicletas', NULL, 'S');
INSERT INTO `categorias` VALUES (38, 'Electrodomésticos', NULL, 'S');
INSERT INTO `categorias` VALUES (40, 'Confort', 38, 'S');
INSERT INTO `categorias` VALUES (42, 'Cocina', 38, 'S');
INSERT INTO `categorias` VALUES (43, 'Limpieza', 38, 'S');
INSERT INTO `categorias` VALUES (44, 'TV / Audio', 38, 'S');
INSERT INTO `categorias` VALUES (46, 'Caballeros', 34, 'S');
INSERT INTO `categorias` VALUES (47, 'Damas', 34, 'S');
INSERT INTO `categorias` VALUES (48, 'Niños', 34, 'S');
INSERT INTO `categorias` VALUES (49, 'Niñas', 34, 'S');
INSERT INTO `categorias` VALUES (51, 'Flores', NULL, 'S');
INSERT INTO `categorias` VALUES (54, 'Celulares', NULL, 'S');
INSERT INTO `categorias` VALUES (59, 'Pasacalles', NULL, 'S');
INSERT INTO `categorias` VALUES (60, 'Juguetes', NULL, 'N');
INSERT INTO `categorias` VALUES (61, 'Muebles', NULL, 'S');
INSERT INTO `categorias` VALUES (62, 'Cámaras Digitales', NULL, 'N');
INSERT INTO `categorias` VALUES (63, 'Perfumes', NULL, 'S');
INSERT INTO `categorias` VALUES (64, 'Masculinos', 63, 'S');
INSERT INTO `categorias` VALUES (65, 'Femeninos', 63, 'S');
INSERT INTO `categorias` VALUES (66, 'Navidad', NULL, 'N');
INSERT INTO `categorias` VALUES (67, 'Cuadros', NULL, 'N');
INSERT INTO `categorias` VALUES (68, 'Mascotas', NULL, 'S');
INSERT INTO `categorias` VALUES (70, 'Hombres', 34, 'N');
INSERT INTO `categorias` VALUES (71, 'Jardín', 38, 'S');
INSERT INTO `categorias` VALUES (72, 'Dia de la Madre', NULL, 'N');
INSERT INTO `categorias` VALUES (73, 'Dia del Padre', NULL, 'N');
INSERT INTO `categorias` VALUES (104, 'Prueba', NULL, 'N');
INSERT INTO `categorias` VALUES (105, 'Juan', 104, 'N');


SET FOREIGN_KEY_CHECKS=1;

Hola Jose_minglein2 ese mismo es el segundo ejemplo que escribi que no funciona. me trae duplicados. Para mi es logico que funcionaba pero no me funciona.

Por cada categoria que tiene NULL trae como si fuera que tiene todos los padres

Prueba con INNER, LEFT o RIGHT JOIN

Saludos