Como ya hemos visto en estos dos artículos:
https://firebird21.wordpress.com/2013/07/19/autorreferenciar-una-tabla-algunos-ejemplos/
podemos escribir una consulta en la cual una tabla se referencia a sí misma. Explayémonos un poco más sobre este tema.
Evidentemente para que referenciemos una tabla con sí misma en ella deben encontrarse los datos que necesitamos. Aquí tenemos dos posibilidades:
- Que la referencia se haga a dos columnas de la tabla
- Que la referencia se haga a una sola columna de la tabla
Quizás habrás notado que en el primero de los dos artículos superiores se usaron dos columnas y en el segundo artículo solamente se usó una columna.
En general, cuando se trata de una tabla maestra (es decir: una tabla que no depende de otras tablas) usamos la primera opción. Y cuando se trata de una tabla de movimientos (es decir: una tabla que depende de otras tablas) usamos la segunda opción. Pero esa no es una regla inmutable, es lo más común pero no está grabado en piedra.
Ejemplo 1:
Necesitamos registrar los datos de algunos animales (por ejemplo: perros) y entre otras cosas queremos saber cual es el padre de cada perro, cual es el abuelo, etc. (lo que en castellano se llama pedigrí)
Para ello podríamos tener una tabla para los hijos, otra para los padres, otra para los abuelos, etc. Pero sería tedioso trabajar así porque tendríamos muchos datos duplicados (un mismo perro podría encontrarse en dos o en tres tablas). Una manera mucho más racional sería tener una sola tabla e identificar en ella al padre de cada perro.
(si haces clic en la imagen la verás más grande)
Y los datos de esa tabla podrían ser algo así:
(si haces clic en la imagen la verás más grande)
Entonces, para ver los nombres de cada perro y los nombres de sus respectivos padres podríamos escribir:
SELECT H.ANI_IDENTI AS "Id. perro", H.ANI_NOMBRE AS "Nombre del perro", H.ANI_FECNAC AS "Fecha nacimiento", P.ANI_IDENTI AS "Id. padre", P.ANI_NOMBRE AS "Nombre del padre" FROM ANIMALES H LEFT JOIN ANIMALES P ON H.ANI_IDEPAD = P.ANI_IDENTI
Como ves, la tabla ANIMALES tiene un JOIN a la tabla ANIMALES, o sea que se referencia a sí misma. Al ejecutar ese SELECT obtendríamos este resultado:
(si haces clic en la imagen la verás más grande)
Donde podemos ver el nombre del padre de cada perro. En algunos casos, también podríamos obtener el nombre del abuelo (INDIO es hijo de AFRO quien a su vez es hijo de SATAN).
SELECT H.ANI_IDENTI AS "Id. perro", H.ANI_NOMBRE AS "Nombre del perro", H.ANI_FECNAC AS "Fecha nacimiento", P.ANI_IDENTI AS "Id. padre", P.ANI_NOMBRE AS "Nombre del padre", A.ANI_IDENTI AS "Id. abuelo", A.ANI_NOMBRE AS "Nombre del abuelo" FROM ANIMALES H LEFT JOIN ANIMALES P ON H.ANI_IDEPAD = P.ANI_IDENTI LEFT JOIN ANIMALES A ON P.ANI_IDEPAD = A.ANI_IDENTI
Si ejecutamos este SELECT esto será lo que obtendremos:
(si haces clic en la imagen la verás más grande)
El cual claramente nos indica que el padre de INDIO es AFRO y que su abuelo es SATAN. De similar manera podríamos obtener los nombres de los perros que no tienen padre (bueno, en la vida real sí lo tienen pero no los tenemos registrados quizás porque no sabemos sus nombres), de los perros que no tienen abuelos, de los perros que son hermanos entre sí, de los hijos de un perro, de los nietos de un perro, etc.
En este ejemplo hemos usado dos columnas de la tabla ANIMALES (ANI_IDENTI y ANI_IDEPAD) para hacer la referencia.
Ejemplo 2:
Queremos conocer los nombres de los alumnos que se han aplazado en Matemática y también se han aplazado en Historia. Las calificaciones van del 0 al 100 y quienes obtienen menos de 40 puntos se aplazan.
La estructura de nuestra tabla de CALIFICACIONES es la siguiente:
(si haces clic en la imagen la verás más grande)
Y estas son algunas de sus filas:
(si haces clic en la imagen la verás más grande)
Entonces, para ver los nombres de los alumnos que se aplazaron en Matemática y también en Historia podríamos escribir:
SELECT C.CAL_IDEALU AS "Id. alumno", A.ALU_NOMBRE AS "Nombre del alumno", C.CAL_NOTAXX AS "Matemática", D.CAL_NOTAXX AS "Historia" FROM CALIFICACIONES C JOIN CALIFICACIONES D ON C.CAL_IDEALU = D.CAL_IDEALU JOIN ALUMNOS A ON C.CAL_IDEALU = A.ALU_IDENTI WHERE C.CAL_NOTAXX < 40 AND D.CAL_NOTAXX < 40 AND C.CAL_CODMAT = 'MAT' AND D.CAL_CODMAT = 'HIS'
Con lo cual obtendríamos:
(si haces clic en la imagen la verás más grande)
Y así sabríamos que RAQUEL se ha aplazado en ambas materias.
En este ejemplo hemos usado solamente una columna de la tabla CALIFICACIONES (la columna: CAL_IDEALU) para hacer la referencia pero hemos necesitado además de un WHERE para establecer la condición.
Conclusión:
Hay muchos casos en los cuales lo más eficiente que podemos tener es una tabla que se referencia a sí misma. Siempre que podamos meter toda la información que necesitamos en una sola tabla es una posibilidad a considerar, pues evitamos la redundancia y en general las consultas son más rápidas.
Artículos relacionados:
Ejemplo Nº 004 – Usando un INNER JOIN para autoreferenciar una tabla
Autorreferenciar una tabla. Algunos ejemplos
El índice del blog Firebird21 | Firebird SQL
Jul 20, 2013 @ 18:14:07