Iniciar sesiónCerrar
Correo:
Contraseña:
Entrar
Recordar sesión en este navegador
Iniciar sesiónIniciar sesiónCrear cuentaCrear cuenta
LWP » Foros » SQL » ayuda con consulta

SQL - ayuda con consulta

Volver
Nuevo Tema
<<>>
 
Vista:

ayuda con consulta

Publicado por fer (2 intervenciones) el 19/08/2011 14:59:14
estoy teniendo demasiados problemas para realizar una consulta

tengo 2 tablas:

usuarios ( ...., id_despacho)

despachos (id, ..., tipo, padre)

la jerarquia de despachos es la siguiente:

oficina (mas alto)
laboratorio
despacho (mas bajo)

estan todos en una tabla, la columna tipo indica lo que es (O, L, o D), y el padre quien tiene por encima. una oficina tiene varios laboratorios, y un laboratorio varios despachos, y cada hijo solo pertenece a un padre

por otra parte, un usuario puede estar asociado a un despacho, a un laboratorio o a una oficina

lo que busco es una consulta que me muestre un listado tal que:

usuario - oficina - laboratorio - despacho

pudiendo estar algunas columnas a null (en caso de que el usuario este asociado a una oficina o un laboratorio)

el problema esta en que al hacer las left joins con la tabla despachos, los usuarios que estan asociados a oficinas o laboratorios me los repite tantas veces como "hijos" tenga ese laboratorio y oficina (saca todas las combinaciones)

ahora mismo mi consulta esta asi:


select u.id, u.nombre, despacho.ofi_id, despacho.ofi_nom, despacho.lab_id, despacho.lab_nom, despacho.des_id, despacho.des_nom
from usuarios u
left join despachos aux ON aux.id = u.id_despacho
left join (
select o.id as ofi_id, o.nombre as ofi_nom, null as lab_id, null as lab_nom, null as des_id, null as des_nom from despachos o
where o.tipo = 'O'
union
select o.id as ofi_id, o.nombre as ofi_nom, l.id as lab_id, l.nombre as lab_nom, null as des_id, null as des_nom from despachos o
left join despachos l on l.padre = o.id and l.tipo = 'L'
where o.tipo = 'O'
union
select o.id as ofi_id, o.nombre as ofi_nom, l.id as lab_id, l.nombre as lab_nom, d.id as des_id, d.nombre as des_nom from despachos o
left join despachos l on l.padre = o.id and l.tipo = 'L'
left join despachos d on d.padre = l.id and d.tipo = 'D'
where o.tipo = 'O'
) as despacho on aux.id =
case
when aux.tipo = 'O' then o.id
when aux.tipo = 'L' then l.id
when aux.tipo = 'D' then d.id
end
where 1=1


espero que se entienda y podais ayudarme
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder
sin imagen de perfil
Val: 806
Bronce
Ha mantenido su posición en SQL (en relación al último mes)
Gráfica de SQL

ayuda con consulta

Publicado por leonardo_josue (1173 intervenciones) el 19/08/2011 17:13:41
Hola Fer... Tu consulta me resultó bastante interesante, y en realidad se me ocurrieron varias formas de resolverlo. igual y la que te voy a proponer no es la más óptima, pero veamos si te puede servir. Faltó que pusieras algunos datos de ejemplo, para tener el panorama más claro, pero creo que sería mas o menos así. Voy a suponer que tus tablas són más o menos así:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
mysql> SELECT * FROM usuarios;
+------------+-----------+-------------+
| id_usuario | nombre    | id_despacho |
+------------+-----------+-------------+
|          1 | usuario 1 |           1 |
|          2 | usuario 2 |           2 |
|          3 | usuario 3 |           5 |
|          4 | usuario 4 |           8 |
|          5 | usuario 5 |          10 |
+------------+-----------+-------------+
5 rows in set (0.00 sec)
 
mysql> SELECT * FROM despachos;
+-------------+------+-------+-----------------+
| id_despacho | tipo | padre | descripcion     |
+-------------+------+-------+-----------------+
|           1 | O    |  NULL | Oficina 1       |
|           2 | L    |     1 | Laboratorio 1.1 |
|           3 | D    |     2 | Despacho 1.1.1  |
|           4 | O    |  NULL | Oficina 2       |
|           5 | L    |     4 | Laboratorio 2.1 |
|           6 | D    |     5 | Despacho 2.1.1  |
|           7 | D    |     5 | Despacho 2.1.2  |
|           8 | L    |     4 | Laboratorio 2.2 |
|           9 | D    |     8 | Despacho 2.2.1  |
|          10 | D    |     8 | Despacho 2.2.2  |
+-------------+------+-------+-----------------+
10 rows in set (0.00 sec)


Ahora bien, para el caso de las Oficinas, no habría problema, pues sería una consulta directa es decir, simplemente pondrías algo como esto:

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT u.id_usuario, u.nombre, NULL despacho, NULL laboratorio,
    -> T1.descripcion oficina
    -> FROM usuarios U INNER JOIN
    -> (SELECT id_despacho, descripcion FROM despachos WHERE tipo = 'O') T1
    -> ON U.id_despacho = T1.id_despacho;
+------------+-----------+----------+-------------+-----------+
| id_usuario | nombre    | despacho | laboratorio | oficina   |
+------------+-----------+----------+-------------+-----------+
|          1 | usuario 1 |     NULL |        NULL | Oficina 1 |
+------------+-----------+----------+-------------+-----------+
1 row in set (0.00 sec)


La cosa se comienza a complicar para el caso de los laboratorios, Aquí tendrías que hacer una doble unión con la tabla despachos, una para obtener el laboratorio y otra para obtener la oficina (a partir del campo padre) podrías hacerlo más o menos así:

1
2
3
4
5
6
7
8
9
10
11
12
13
mysql> SELECT u.id_usuario, u.nombre, NULL despacho, T1.laboratorio, T1.oficina
    -> FROM usuarios U INNER JOIN
    -> (SELECT D1.id_despacho, D1.descripcion laboratorio, D2.descripcion oficina
    -> FROM despachos D1
    -> INNER JOIN despachos D2 ON D1.padre = D2.id_despacho
    -> WHERE D1.tipo = 'L') T1 ON U.id_despacho = T1.id_despacho;
+------------+-----------+----------+-----------------+-----------+
| id_usuario | nombre    | despacho | laboratorio     | oficina   |
+------------+-----------+----------+-----------------+-----------+
|          2 | usuario 2 |     NULL | Laboratorio 1.1 | Oficina 1 |
|          3 | usuario 3 |     NULL | Laboratorio 2.1 | Oficina 2 |
|          4 | usuario 4 |     NULL | Laboratorio 2.2 | Oficina 2 |
+------------+-----------+----------+-----------------+-----------+


Lo interesante en esta consulta es la subconsulta para T1, Puedes ejecutar esa subconsulta por separado para que veas cómo está funcionando.

Finalmente para el último nivel, el de despachos, utilizamos la misma subconsulta para T1 anterior, pero agregando un nuevo INNER JOIN para el último nivel, es decir algo como esto:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> SELECT u.id_usuario, u.nombre, T1. despacho, T1.laboratorio, T1.oficina
    -> FROM usuarios U INNER JOIN
    -> (SELECT D3.id_despacho, D3.descripcion despacho, D4.laboratorio, D4.oficina
    -> FROM despachos D3
    -> INNER JOIN
    -> (SELECT D1.id_despacho, D1.descripcion laboratorio, D2.descripcion oficina
    -> FROM despachos D1
    -> INNER JOIN despachos D2 ON D1.padre = D2.id_despacho
    -> WHERE D1.tipo = 'L') D4 ON D3.padre = D4.id_despacho WHERE D3.tipo = 'D') T1
    -> ON U.id_despacho = T1.id_despacho
+------------+-----------+----------------+-----------------+-----------+
| id_usuario | nombre    | despacho       | laboratorio     | oficina   |
+------------+-----------+----------------+-----------------+-----------+
|          5 | usuario 5 | Despacho 2.2.2 | Laboratorio 2.2 | Oficina 2 |
+------------+-----------+----------------+-----------------+-----------+
1 row in set (0.00 sec)


Nuevamente observa que la subconsulta T2, utiliza la consulta del paso dos agregando un nuevo INNER JOIN para el último nivel. Lo unico que restaría hacer es un UNION entre las tres subconsultas, de tal suerte que el resultado sería:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
mysql> SELECT u.id_usuario, u.nombre, NULL despacho, NULL laboratorio,
    -> T1.descripcion oficina
    -> FROM usuarios U INNER JOIN
    -> (SELECT id_despacho, descripcion FROM despachos WHERE tipo = 'O') T1
    -> ON U.id_despacho = T1.id_despacho
    -> UNION
    -> SELECT u.id_usuario, u.nombre, NULL despacho, T1.laboratorio, T1.oficina
    -> FROM usuarios U INNER JOIN
    -> (SELECT D1.id_despacho, D1.descripcion laboratorio, D2.descripcion oficina
    -> FROM despachos D1
    -> INNER JOIN despachos D2 ON D1.padre = D2.id_despacho
    -> WHERE D1.tipo = 'L') T1 ON U.id_despacho = T1.id_despacho
    -> UNION
    -> SELECT u.id_usuario, u.nombre, T1. despacho, T1.laboratorio, T1.oficina
    -> FROM usuarios U INNER JOIN
    -> (SELECT D3.id_despacho, D3.descripcion despacho, D4.laboratorio, D4.oficina
    -> FROM despachos D3
    -> INNER JOIN
    -> (SELECT D1.id_despacho, D1.descripcion laboratorio, D2.descripcion oficina
    -> FROM despachos D1
    -> INNER JOIN despachos D2 ON D1.padre = D2.id_despacho
    -> WHERE D1.tipo = 'L') D4 ON D3.padre = D4.id_despacho WHERE D3.tipo = 'D') T1
    -> ON U.id_despacho = T1.id_despacho;
+------------+-----------+----------------+-----------------+-----------+
| id_usuario | nombre    | despacho       | laboratorio     | oficina   |
+------------+-----------+----------------+-----------------+-----------+
|          1 | usuario 1 | NULL           | NULL            | Oficina 1 |
|          2 | usuario 2 | NULL           | Laboratorio 1.1 | Oficina 1 |
|          3 | usuario 3 | NULL           | Laboratorio 2.1 | Oficina 2 |
|          4 | usuario 4 | NULL           | Laboratorio 2.2 | Oficina 2 |
|          5 | usuario 5 | Despacho 2.2.2 | Laboratorio 2.2 | Oficina 2 |
+------------+-----------+----------------+-----------------+-----------+
5 rows in set (0.01 sec)


Como verás la consulta es bastante compleja y si manejas un numero importante de registros es posible que no resulte del todo eficiente, pero como te comenté, es una de las posible soluciones al problema. No nos mencionas qué DBMS utilizas, pero algunos de ellos implementan funcionalidades particulares para el uso de tablas jerárquicas, es cuestión de que investigues algo más acerca de este tipo de consultas.

El código está hecho para MySQL, pero debería ser compatible con la mayoría de los DBMS's.

Saludos y espero tus comentarios.
Leo.
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

ayuda con consulta

Publicado por fer (2 intervenciones) el 22/08/2011 09:14:36
muchas gracias leo, me ha funcionado a la perfección

aun no veo porque no funcionaba la mia pero me da igual ya, he perdido demasiado tiempo en esta consulta

la diferencia más destacable es que tu has anidado varias INNERs, y yo las ponia todas al mismo nivel. algo de eso tiene que ser, pero lo dicho, lo importante es que ya me funciona

gracias de nuevo
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar