COnsulta a tabla autorelacionada

[amj]

Hola, por adelantado gracias por ayudarme con este dilema, no se que tan factible sea.

Tengo la siguiente consulta:

select p.pais_id,p.pais.name,d.reg_id,d.reg_name,c.extens ion,c2.extension from
pais p
full join region d on d.pais = p.pais_id
full join campo c on c.region =d.reg_id
full join campo c2 on c2.parent = c.id
where p.enabled = 1

La tabla campo se autorelaciona asi misma pero cuando tengo datos anidados no salen en la consulta, como puedo solucionar esto.


Mil Gracias

[huesos52]

Que tal amj...

Para entender un poco mas el problema, puedes compartirnos la estructura de tus tablas?
Trabajas en oracle?

Un saludo.

[amj]

Hola gracias por responder, si trabajo en oracle.

pais
pais_id PK number
pais_name string
pais_ext string
pais_enabled number

region
reg_id PK number
reg_name string
reg_extension string
reg_pais FK number

campo
id PK number
extension string
parent FK number
region FK number

la tabla campo se autorelaciona asi misma con el campo parent y se relaciona con region

Gracias

[huesos52]

amj...

No estoy seguro si funcione, no tengo como probarlo en este momento, pero prueba así:

Código sql:

SELECT p.pais_id,p.pais.name,d.reg_id,d.reg_name,c.extens ion,c2.extension FROM
pais p
FULL JOIN region d ON d.pais = p.pais_id
FULL JOIN campo c ON c.region =d.reg_id
FULL JOIN campo c2 ON c2.parent = c.id AND c2.region = d.reg_id
WHERE p.enabled = 1
 

Unos pocos registros de muestra y un ejemplo practico de lo que pasa, no caería mal.

Un saludo amj.

[amj]

Hola huesos52, gracias por tus respuestas, por ejemplo tengo en

Código pre:

pais:
 
pais_id     pais_name    pais_ext    pais_enabled
1              USA               1                1
2              Canada         2                1
 
Region
 
reg_id     reg_name      reg_extension     reg_pais
1             SF                  712                      1
2             NY                  815                      1
 
Campo
 
id       extension       parent      region
1        88999988                       1
2        762                1
3        23                  2
4        99988877                       2
 

De manera que me gustaria hacer una consulta donde me aloje

Código pre:

p.pais_id    p.pais_name    d_reg_id     d.reg_name   c.extension   c2.extension
1                USA          1             SF        88999988           762
1                USA          1             SF        762              23
1                USA          2             NY        99988877
2                Canada
 

[huesos52]

amj

tengo cierta confusión en los registros de la tabla campo.
Campo

id-------extension------parent------region
1-------88999988------------------ 1
2-------762-------------- 1-------------
3------- 23--------------- 2--------------
4-------99988877---------------------2

Si parent es el campo que está autorelacionado con id del campo,como es posible que tengas dos espacios vacíos en el primer y cuarto registro. No sería una violación a la integridad referencial?

No son necesarios estos registros?

[amj]

no porque ellos son los primeros nodos, y parent relaciona a los id de la autorelacion

[huesos52]

Que tal amj

Debes tener en cuenta que si usas nodos y consultas jerarquicas, estas tienen formas de acceso a nivel de consulta.

Prueba esto:

Código sql:

SELECT p.pais_id,p.pais_name,d.region_id,d.region_name,c.extension,c2.extension FROM
pais p
FULL JOIN region d ON d.reg_pais = p.pais_id
FULL JOIN campo c ON c.region = d.region_id
FULL JOIN (SELECT *FROM campo START WITH parent IS NOT NULL CONNECT BY prior campo_id=parent) c2 
ON c2.parent >= c.campo_id 
WHERE p.pais_enabled = 1 GROUP BY
p.pais_id,p.pais_name,d.region_id,d.region_name,c.extension,c2.extension
ORDER BY 1;
 

La parte esencial de la consulta, es establecer el full join con el resultado de los nodos.
Con las clausulas start with y connect by puedes acceder al valor de los nodos pertenecientes al nodo raiz.

Te dejo un poco de información:
http://www.adp-gmbh.ch/ora/sql/connect_by.html


Por cierto, cambié un poco los campos. ten cuidado con eso.


Espero te sirva.

[amj]

voy a probar en estos momentos lo tengo hecho con un while a traves del programa cosa que no me gusta porque podira llegar a ser muchos selects, gracias huesos52

[amj]

Hola de nuevo probe y funciona al menos ya redusco el codigo pero en la consulta

SELECT *FROM campo start WITH parent IS NOT NULL connect BY prior campo_id=parent

hay alguna forma de decirle en el start width sea con paren = c.campo_id

pues en esa consulta retornara todo el arbol de todas la regiones, y pues entonces voy a tener extensiones que no pertenece a una region.

[huesos52]

Amj...
La verdad tu tema me inquietó bastante, nunca lo había leído. Por lo poco que he entendido, las relaciones jerárquicas se consideran con un único nodo root. De root pueden surgir parents que es el caso en este ejemplo. Estos a su vez, tener hijos y los hijos hojas. Por la forma como me mostraste el ejemplo de la salida que quieres, parece ser que tuvieras dos nodos root.

Con respecto a lo que preguntas, cuando se inicia un nodo, se hace recursivamente, es por esto, que si empiezas en 1 va a recorrer todo el árbol.

En mi concepto, te haría falta llenar un poco mas tu tabla.

Te comparto un ejemplo, donde comprendí el funcionamiento de nodos... (Como es de un libro te lo escribo)

Código sql:

SELECT *
FROM more_employees;
EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME TITLE SALARY
----------- ---------- ---------- ---------- ------------- ----------
1 James Smith CEO 800000
2 1 Ron Johnson Sales Manager 600000
3 2 Fred Hobbs Sales Person 200000
4 1 Susan Jones Support Manager 500000
5 2 Rob Green Sales Person 40000
6 4 Jane Brown Support Person 45000
7 4 John Grey Support Manager 30000
8 7 Jean Blue Support Person 29000
9 6 Henry Heyson Support Person 30000
10 1 Kevin Black Ops Manager 100000
11 10 Keith Long Ops Person 50000
12 10 Frank Howard Ops Person 45000
13 10 Doreen Penn Ops Person 47000
 

Fijate como se establecen los nodos.

Código sql:

SELECT employee_id, manager_id, first_name, last_name
FROM more_employees
START WITH employee_id = 1
CONNECT BY PRIOR employee_id = manager_id;
EMPLOYEE_ID MANAGER_ID FIRST_NAME LAST_NAME
----------- ---------- ---------- ---------
1 James Smith
2 1 Ron Johnson
3 2 Fred Hobbs
5 2 Rob Green
4 1 Susan Jones
6 4 Jane Brown
9 6 Henry Heyson
7 4 John Grey
8 7 Jean Blue
10 1 Kevin Black
11 10 Keith Long
12 10 Frank Howard
13 10 Doreen Penn
 

En este ejemplo, es claro que james smith es el nodo root. Y que ron, susan y kevin son los parents. Es así como funciona la estructura de nodos.

Si el start with lo empezaras cuando first_name = 'susan' solo mostraría la parte del árbol por debajo de susan. (osea jhon grey, jane brown, henry heison, john gray y jean blue)

Si quieres, puedes poner un poco mas de datos, de que forma los llenas y que criterios utilizas para establecer un registro como nodo de otro.

[amj]

Hola huesos52, al parte como inicia el start te lo comprendo y es por eso que quiero que el star width en lugar de empezar cuando es null esto es recorreindo todo el arbol, me gustaria que iniciara cuando parent = c.campo_id o cuando region = d.region_id, pero cuando lo hago me sale error de que no se reconoce c.campo_id o d.region_id.

Código sql:

SELECT p.pais_id,p.pais_name,d.region_id,d.region_name,c.extension,c2.extension FROM
pais p
FULL JOIN region d ON d.reg_pais = p.pais_id
FULL JOIN campo c ON c.region = d.region_id
FULL JOIN (SELECT *FROM campo START WITH parent = c.campo_id CONNECT BY prior campo_id=parent) c2 
ON c2.parent >= c.campo_id 
WHERE p.pais_enabled = 1 GROUP BY
p.pais_id,p.pais_name,d.region_id,d.region_name,c.extension,c2.extension
ORDER BY 1;
 

[huesos52]

No creo que sea posible. Pero la cosa es que en el start with se le entrega un parametro de inicio. c.campo_id tendría muchos valores de inicio.
Cual sería el objetivo de iniciarlo en c.campo_id?

[amj]

Hola huesos52, pues como ves c.campo_id viene de la relacion anterior, por lo caul es el nodo parent para la siguiente relacion, de todas maneras ya me gustado mucho esta solucion pues solo tengo dos consultas. Muchas gracias huesos52

[huesos52]

De nada