Consulta jerárquica

[arsenal]

Hola buenas noches gente,

Veran estoy creando un bdd con una estructura jerárquica y relacional

Código MySQL:

CREATE TABLE `categorias` (
  `id` int(3) NOT NULL auto_increment,
  `nombre` varchar(140) NOT NULL,
  `pariente` int(3) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
 
-- 
-- Volcar la base de datos para la tabla `categoria`
-- 
 
INSERT INTO `categorias` VALUES (1, 'papa', NULL);
INSERT INTO `categorias` VALUES (2, 'hijo', 1);
INSERT INTO `categorias` VALUES (3, 'nieto', 2);
INSERT INTO `categorias` VALUES (4, 'bisnieto', 3);
 
-- 
-- Volcar la base de datos para la tabla `categoria`
-- 
 
CREATE TABLE `subcategorias_negocios` (
  `id` int(5) NOT NULL auto_increment,
  `negocio` int(8) NOT NULL,
  `subcategoria` int(3) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;
 
INSERT INTO `subcategorias_negocios` VALUES (1, 1, 1);
INSERT INTO `subcategorias_negocios` VALUES (2, 2, 1);
INSERT INTO `subcategorias_negocios` VALUES (3, 4, 2);
INSERT INTO `subcategorias_negocios` VALUES (4, 5, 3);
INSERT INTO `subcategorias_negocios` VALUES (5, 6, 4);
 

Bien como pueden ver en la tabla categorias el registro con NULL en el campo pariente será la categoria padre o principal.

Yo quisiera sacar el total de negocios que tengo en la categoria principal.

De acuerdo a subcategorias_negocios deberia regresarme y decirme que tengo 6 negocios en la categoria principal (Padre de la tabla categoria categorias)

Como podría ser está consulta?


Saludos

[quimfv]

Revisa bien lo que dices... porque si tienes solo 5 registros dificilmente te van a salir 6... por otro lado no tienes definida ninguna relacion entre las dos tablas que muestras con lo que no podemos adivinar como las quieres relacionar. Y finamente en la tabla parientes normalmente se definen dos campos mas "nivel" y "orden" para poder gestionarla.

El primero, nivel, se usa para guardar la distancia con el nodo principal, los hijos tendrian nivel 1, nietos 2.... no digo que no se pueda calcular pero ....

Y orden para determinar el orden entre nodos del mismo nivel...este se podria obviar si por ejemplo te sirve el orden alfabetico sobre nombre o en un caso mas real tienes una fecha de creación o nacimiento para ordenarlos.

[arsenal]

Hola quimfv, tienes razón me fije mal y si tendria que arrojarme 5 negocios en total, la relacion que tengo entre las dos tablas es ID de la tabla categorias con el campo SUBCATEGORIA de la tabla subcategorias_negocios en cuento a nivel sería hasta 4 niveles máximo o sea padre > hijo > nieto > bisnieto es decir :

categoria princila (padre)
----------subcategoria de padre (hijo)
--------------------subcategoria de subcategoria de padre (nieto)
------------------------------subcategoria de subcategoria de subcategoria de padre (bisnieto)

y orden seria por alfabeto,

mil disculpas por olvidar estos datos

Saludos y gracias de antemano

[quimfv]

Si X es el padre que buscas

Código MySQL:

SELECT id,
   negocio 
FROM subcategorias_negocios 
WHERE subcategoria=X
UNION ALL
SELECT sn.id,
   sn.negocio 
FROM subcategorias_negocios sn INNER JOIN categorias c
    ON sn.subcategoria=c.id 
WHERE c.pariente=X
UNION ALL
SELECT sn.id,
  sn.negocio 
FROM subcategorias_negocios sn INNER JOIN categorias c
  ON sn.subcategoria=c.id 
WHERE c.pariente IN (SELECT c.id 
                              FROM categorias c
                               WHERE c.pariente=X)
UNION ALL
SELECT sn.id,
  sn.negocio 
FROM subcategorias_negocios sn INNER JOIN categorias c
  ON sn.subcategoria=c.id 
WHERE c.pariente IN (SELECT c.id
                         FROM categorias c
                         WHERE pariente IN (SELECT c.id 
                                             FROM categorias c
                                                       WHERE c.pariente=X));
 

Si padre=X esta consulta te dará los negocios de X y sus decendientes.

Queda claro que esto solo sirve para una estructura como la que planteas donde hay solo 4 y no mas niveles jerarquicos. Y no existen descendientes sin ascendentes (excepto en el primer nivel).

[arsenal]

Muchas gracias quimfv ahora mismo lo checo pero a como lo veo esta consulta me mostrará todos los negocios que tenga padre x cierto?

Yo solo queria sacar el total de negocios en categoría padre pero en número asi como contador, o sea contar el total de registros en esa categoría por ejemplo Alimentos (10) donde 10 es el número total de negocios en esa categoría y es que aqui ya no se como meterle el count o sumo o como .

Esta me servirá para mostrar los negocios :)

Saludos y gracias de antemano

[arsenal]

Código MySQL:

SELECT id,
       negocio, count(*) total
    FROM subcategorias_negocios
    WHERE subcategoria=X
    UNION ALL
    SELECT sn.id,
       sn.negocio
    FROM subcategorias_negocios sn INNER JOIN categorias c
        ON sn.subcategoria=c.id
    WHERE c.pariente=X
    UNION ALL
    SELECT sn.id,
      sn.negocio
    FROM subcategorias_negocios sn INNER JOIN categorias c
      ON sn.subcategoria=c.id
    WHERE c.pariente IN (SELECT c.id
                                  FROM categorias c
                                   WHERE c.pariente=X)
    UNION ALL
    SELECT sn.id,
      sn.negocio
    FROM subcategorias_negocios sn INNER JOIN categorias c
      ON sn.subcategoria=c.id
    WHERE c.pariente IN (SELECT c.id
                             FROM categorias c
                             WHERE pariente IN (SELECT c.id
                                                 FROM categorias c
                                                           WHERE c.pariente=X));
 

no logro obtener el total tambien creo que no necesito especificar el negocio si no que me arroje todas las categorias con existententes (categorias padre) con su total

[quimfv]

Código MySQL:

SELECT sbc.id,sbc.negocio,count(*)
FROM (SELECT id,
       negocio
    FROM subcategorias_negocios
    WHERE subcategoria=X
    UNION ALL
    SELECT sn.id,
       sn.negocio
    FROM subcategorias_negocios sn INNER JOIN categorias c
        ON sn.subcategoria=c.id
    WHERE c.pariente=X
    UNION ALL
    SELECT sn.id,
      sn.negocio
    FROM subcategorias_negocios sn INNER JOIN categorias c
      ON sn.subcategoria=c.id
    WHERE c.pariente IN (SELECT c.id
                                  FROM categorias c
                                   WHERE c.pariente=X)
    UNION ALL
    SELECT sn.id,
      sn.negocio
    FROM subcategorias_negocios sn INNER JOIN categorias c
      ON sn.subcategoria=c.id
    WHERE c.pariente IN (SELECT c.id
                             FROM categorias c
                             WHERE pariente IN (SELECT c.id
                                                 FROM categorias c
                                                           WHERE c.pariente=X))) sbc
GROUP BY sbc.id,sbc.negocio;
 

Ahora...

[arsenal]

Hola Quim mucha gracias por tu ayuda nada mas hay algo que no logro entender y es por que X? en esta parte subcategoria=X ?

Ya miro y veo muchas gracias por tu ayuda y su tiempo. ya le cuento

[quimfv]

Primero buscas los negocios de X, subcategoria=X, luego los de los hijos de X, pariente=X, luego los nietos, pariente IN ( ... pariente=X )

[arsenal]

Muchas gracias quimfv ahi quedo funcionando al 100

Saludos