consultas con relaciones many-to-many ?

[skanskan]

Buenas.

Estoy estudiando/repasando un poco bases de datos.
Para crear relaciones many-to-many entre dos tablas A y B se suele crear una tercera tabla C (llamada pivot table, joint table o de otras maneras) con campos de A y de B. Y se crean relaciones 1-to-many entre esa tabla C con A y entre C y B.
Eso lo he visto en algún texto y en video.

Pero lo que no encuentro es como usar luego esas tablas.

Por ejemplo si tenemos información que relaciona a los padres con sus hijos y a los alumnos con sus hijos (pudiendo tener cada padre varios hijos y cada hijo varios padres (divorciados)) ...
¿Qué consulta usariamos en SQL para saber que padres tiene un niño?
¿Qué consularía usaremos para saber que hermanos tiene un niño?
¿Alguna solución más simple?

Por ejemplo

Padres, Hijos:
Pepe, Josito
Pepe, Anita
Maria, Josito
Sofia, Anita
Sofia, Vicentito
Manolo, Vicentito

(nombres iguales en este caso son la misma persona).

Esta pregunta la hago en la sección de SQL Server pero me sirve también una respuesta para MySQL, Access, etc...
Cualquier otro ejemplo me sirve igual.

saludos.

[Libras]

Supongo que esa es la tabla que obtienes de unir los datos de padres, hijos y relacion digamos que tu esquema esta de esta manera:

Código SQL:

CREATE TABLE #padres
(
id INT IDENTITY(1,1),
nombre VARCHAR(30)
)
 
CREATE TABLE #hijos
(
id INT IDENTITY(1,1),
nombre VARCHAR(30)
)
 
CREATE TABLE #relacion
(
id_padre INT,
id_hijo INT
)
 
INSERT INTO #padres VALUES ('Pepe')
INSERT INTO #padres VALUES ('Sofia')
INSERT INTO #padres VALUES ('Manolo')
INSERT INTO #padres VALUES ('Maria')
 
INSERT INTO #hijos VALUES ('Josito')
INSERT INTO #hijos VALUES ('Anita')
INSERT INTO #hijos VALUES ('Vicentito')
 
INSERT INTO #relacion VALUES (1,1)
INSERT INTO #relacion VALUES (1,2)
INSERT INTO #relacion VALUES (4,1)
INSERT INTO #relacion VALUES (2,2)
INSERT INTO #relacion VALUES (2,3)
INSERT INTO #relacion VALUES (3,3)
 
SELECT t1.nombre,t3.nombre FROM #padres AS t1
LEFT JOIN #relacion AS t2 ON (t2.id_padre=t1.id)
LEFT JOIN #hijos AS t3 ON (t2.id_hijo=t3.id)
 

Claro que TU tienes que llenar la tabla de relaciones ya que no existe una forma magica para que se llene ;)

Aqui esta para sql server :)
saludos!

[skanskan]

Voy a estudiar un rato esa sentencia SQL.
Mientras tanto ¿Cómo evitas que aparezca el nombre de un niño como hermano de sí mismo?

saludos

[Libras]

Digamos para sacar los hijos de maria y pepe(usando el ejemplo)

Código SQL:

--Aqui sacas los nombres, pero Josito se repite porque es hijo de pepe y maria,aunque anita nada mas es hija de pepe ???? raro verdad :P
SELECT t3.nombre FROM #padres AS t1
LEFT JOIN #relacion AS t2 ON (t2.id_padre=t1.id)
LEFT JOIN #hijos AS t3 ON (t2.id_hijo=t3.id)
WHERE t1.nombre IN ('pepe','maria')
 
--Aplicando un distinct nada mas obtenemos 2 nombres que son Josito y Anita
SELECT DISTINCT t3.nombre FROM #padres AS t1
LEFT JOIN #relacion AS t2 ON (t2.id_padre=t1.id)
LEFT JOIN #hijos AS t3 ON (t2.id_hijo=t3.id)
WHERE t1.nombre IN ('pepe','maria')
 

[skanskan]

Hola y gracias.
En el viejo portátil de mi novia no puedo poner SQL Server pero he conseguido más o menos ejecutarlo con algunos cambios online en la web w3schools.
La principal duda que me surge es ¿Por qué usas joins y no solamente where?
¿Quiero decir, para indicar relaciones no deberíamos usar foreign keys?

[Libras]

Iniciado por skanskan

Hola y gracias.
En el viejo portátil de mi novia no puedo poner SQL Server pero he conseguido más o menos ejecutarlo con algunos cambios online en la web w3schools.
La principal duda que me surge es ¿Por qué usas joins y no solamente where?
¿Quiero decir, para indicar relaciones no deberíamos usar foreign keys?

Las relaciones como las mencionas se manejan a nivel de DDL, cuando se trabaja con DML entonces es necesario usar joins

[skanskan]

Hola.
Acabo de googlear DDL y DML y creo entender que con ello simplemente te refieres a que los "foreign key" sólo se usa al crear las tablas y no al hacer queries después. Pero es que en tu ejemplo no has usado ninguna al definirlas, por eso lo decía.

[Libras]

Iniciado por skanskan

Hola.
Acabo de googlear DDL y DML y creo entender que con ello simplemente te refieres a que los "foreign key" sólo se usa al crear las tablas y no al hacer queries después. Pero es que en tu ejemplo no has usado ninguna al definirlas, por eso lo decía.

Aaaaa si te explicas te entiendo, porque no lo use en mi ejemplo?? la verdad porque no quise :P jejejejejejeje, y porque son datos de ejemplo, en un ambiente donde realmente se van a validar las relaciones claro que lleva las FK

[gnzsoloyo]

Iniciado por skanskan

Hola.
Acabo de googlear DDL y DML y creo entender que con ello simplemente te refieres a que los "foreign key" sólo se usa al crear las tablas y no al hacer queries después. Pero es que en tu ejemplo no has usado ninguna al definirlas, por eso lo decía.

Estimado: Las FK son la forma en que en las bases de datos relacionales defnes qué relacion hay entre entidades o tablas, para luego usar esas relaciones para las consultas.
¿De dónde sacaste que luego no las usas?

AL determinar una FK, estás definiendo también una dependencia funcional entre datos, y una restricción de consistencia que evitará que falten datos en el esquema de dependencias. En pocas palabras, las FK son el corazón del modelo relacional, y fundamentales apra la generacion de consultas.

Si el tema de consistencia e integridad no te ha quedado claro, te recomienod enfáticamente que estudies los fundamentos del modelo relacional, antes de rposeguir desarrollos, a fin de evitarte enormes problemas futuros.

[skanskan]

Hola.
Si tengo claro que las FK sirven para asegurar la integridad, para hacer cosas como impedir que borres algo que está referenciado en otro sitio, para actualizar en cascada, para crear una correspondencia uno a uno entre campos, etc.
Pero lo que me gustaría es ver como afecta esto (y como se usa) a las consultas, cuando ya has creado las tablas y has definido las relaciones, ¿y luego qué?.

saludos.

[gnzsoloyo]

Pues simplemente las usas para generarlos JOIN basados en esas relaciones... Francamente me cuesta ver por qué te plantea dudas.

Si tienes una tabla A(a_id, a_x, a_y, a_z) y otra B(a_id, b_x, b_y, b_z), donde hay una manifiesta relación de 1:N entrew A y B, determinada por el campo "A_ID", un JOIN que te devuelva las que se relacionan sería simplemente:

Código SQL:

SELECT A.*, B.*
FROM A INNER JOIN B ON A.a_id = B.a_id
 

Y las que están en A pero no en B:

Código SQL:

SELECT A.*, B.*
FROM A LEFT JOIN B ON A.a_id = B.a_id
WHERE B.a_id IS NULL
 

Además, el hecho de que existan FK genera mejoras de performance, desde el momento en que los DBMS habitualmente manejan esa dependencia como índices, que tienen impacto directo en los planes de consulta que se generan internamente.
Cualquier búsqueda basada en índice de FK ers mucho más eficiente que una busqueda sin usarlos.

Esto es sólo una parte de las ventajas que tienen y los usos de las FK.

[skanskan]

1
¿Pero se podría hacer esa misma consulta, con el mismo resultado, sin haber usado la FK?
¿Sólo afectará entonces al rendimiento y a la hora de poblar las tablas?
En mi ejemplo de hijos y padres por ejemplo, Puedo hacer todo tipo de consultas sin haber definido FK, y saber cosas como que padres tiene un hijo.


2
En tu ejemplo has puesto:

WHERE t1.nombre IN ('pepe','maria')

pero para eso tengo que escribir esos nombres en la propia consulta.
¿Cómo hago para que los vaya cogiendo "en tiempo de ejecución" de una lista o de los generados de otro sitio?
Por ejemplo, ¿Cómo generas una tabla en la que apareza para cada niño todos los hermanos que tiene, sin que aparezca su propio nombre?

Usando elementos de batch de DOS sería algo así
for %f in hijos do SELECT hijos.nombre AS s1 WHERE s1 <> %f

¿como hago algo así con sql?.
O hablando de otros lenguajes de programación habría que usar dos bucles o foreach...

[Libras]

1.-Exacto, como bien te dije en DML(querys) no ocupas las FK, las FK son solo para la parte de DDL
2.-Aqui pongo los padres pero se puede generar lo que necesitas, pero en el ejemplo que manejo nada mas estan los padres(general) no se cuales son pareja y cuales solteros correcto? asi que digamos que agregamos una columna id pareja(solo para ejemplo) y podemos obtener esto:

Código SQL:

CREATE TABLE #padres
(
id INT IDENTITY(1,1),
nombre VARCHAR(30),
id_pareja INT
)
 
CREATE TABLE #hijos
(
id INT IDENTITY(1,1),
nombre VARCHAR(30)
)
 
CREATE TABLE #relacion
(
id_padre INT,
id_hijo INT
)
 
INSERT INTO #padres VALUES ('Pepe',1)
INSERT INTO #padres VALUES ('Sofia',1)
INSERT INTO #padres VALUES ('Manolo',2)
INSERT INTO #padres VALUES ('Maria',2)
 
INSERT INTO #hijos VALUES ('Josito')
INSERT INTO #hijos VALUES ('Anita')
INSERT INTO #hijos VALUES ('Vicentito')
 
INSERT INTO #relacion VALUES (1,1)
INSERT INTO #relacion VALUES (1,2)
INSERT INTO #relacion VALUES (4,1)
INSERT INTO #relacion VALUES (2,2)
INSERT INTO #relacion VALUES (2,3)
INSERT INTO #relacion VALUES (3,3)
 
SELECT 
DISTINCT t1.id_pareja,t3.nombre
 FROM #padres AS t1
LEFT JOIN #relacion AS t2 ON (t2.id_padre=t1.id)
LEFT JOIN #hijos AS t3 ON (t2.id_hijo=t3.id)
WHERE t1.id IS NOT NULL
 

Y la traduccion de tu batch:

Código BASH:

for %f in hijos do SELECT hijos.nombre AS s1 WHERE s1 <> %f
 

seria:

Código SQL:

SELECT distint nombre FROM #hijos
 

Como comentario, no puedes aplicar la misma logica de programacion en una base de datos, esta cambia...ojo con eso.....

[skanskan]

Hola
Es que ese

Código:

SELECT distint nombre FROM #hijos

lo que hace es que elimina los repetidos pero comparandolos entre sí, no con el hermano original.

Por ejemplo. Si tengo como hermanos
Manolo, Maria, Jose y Vicente y otro Vicente.
Me daría como hermanos de Manolo
Maria, Jose, un sólo Vicente y al propio Manolo.

[Libras]

la verdad no entiendo tu pregunta........que quieres decir con hermano original??? como se quien es el hermano original??? tienes alguna estructura de base de datos o nada mas estas preguntando al vuelo y pensando de manera cognositiva???

[gnzsoloyo]

Estás mirando las cosas como programador, y debes analizarlo desde la visión del DBA o de arquitectura de datos. Son lógicas diferentes.

Vamos a hacer algunas aclaraciones para evitar confusiones:

Iniciado por skanskan

1
¿Pero se podría hacer esa misma consulta, con el mismo resultado, sin haber usado la FK?
¿Sólo afectará entonces al rendimiento y a la hora de poblar las tablas?
En mi ejemplo de hijos y padres por ejemplo, Puedo hacer todo tipo de consultas sin haber definido FK, y saber cosas como que padres tiene un hijo.

Poderse, se puede, como ya te dijeron. La pregunta es si esa query será eficiente ("performante" se suele decir), o si al menos segura. Y la respuesta es no mucho.
La idea de una FK es que se pueda, por medio de un atributo de una entidad, establecer de forma unívoca una relación. Esto quiere decir que la información que se devuelva sea consistente e íntegra, y para ello los datos a relacionar deben ser seguros.
¿Puedes hacer una relación entre dos instancias de una tabla, si los datos no son seguros? ¿Si pueden darse ambigüedades en cuanto a alguna respuesta?
No. Por definición de lo que es una PK, sólo debe identificar a un único registro en todo el universo de representaciones, y siendo la FK una referencia a una PK, esa relación debe existir, ser válida y única.
¿Con qué otra cosa que no sea una relación de FK/PK lo haces en este modelo?

Simple: Con ninguna.

Ahora bien, si puedes relacionar dos instancias de dos entidades sin usar las FK, pero esa identificación es segura y consistente, te comento que no estás ante otra forma de relacionar ambas tablas. Estás ante la misma forma de relacionarlas, pero eso se denomina "clave alternativa", y está dentro del modelo.
El hecho concreto es que una instancia de na entidad puede tener N formas de ser identificada unívocamente, pero sólo una se define como PK. Las otras podrían ser usadas como claves UNIQUE, que en algunos DBMS se admiten para uso como FK (MySQL, por ejemplo), porque cumplen la premisa de ser identificadores únicos no nulos.

¿Se entiende?

Lo que sí se puede asegurar es que usar otros campos no claves, pero de valor único, como relación en un JOIN no es eficiente, pero dependerá de lo que se esté consultando.

Iniciado por skanskan

2
En tu ejemplo has puesto:

WHERE t1.nombre IN ('pepe','maria')

pero para eso tengo que escribir esos nombres en la propia consulta.
¿Cómo hago para que los vaya cogiendo "en tiempo de ejecución" de una lista o de los generados de otro sitio?
Por ejemplo, ¿Cómo generas una tabla en la que apareza para cada niño todos los hermanos que tiene, sin que aparezca su propio nombre?

Para eso existen las subconsultas en el WHERE, los auto-JOIN y otros recursos que aparecen en los manuales, y se aprenden con la experiencia. Escribir consultas no es poner "SELECT * FROM ... WHERE...". Trabajar con SQL es muchísimo más complejo que eso.

Iniciado por skanskan

Usando elementos de batch de DOS sería algo así
for %f in hijos do SELECT hijos.nombre AS s1 WHERE s1 <> %f

¿como hago algo así con sql?.
O hablando de otros lenguajes de programación habría que usar dos bucles o foreach...

No razones como programador cuando estés trabajando con BBDD. La lógica que se usa para resolver las consultas no es la misma que para resolver un proceso.
Esto no es una fantasía ni una exageración, sino las primeras palabras que los profesores de BBDD en la universidad te dicen, y la experiencia me ha enseñado que tienen razon.
Trabajar con estructuras de datos, y esquemas de BBDD, no es lo mismo que trabajar con procesos y programación en lenguajes.
Tienes que aprender a dejar de lado lo que haces programando para entender la lógica de datos, y no pretender "adaptar" algo de un lenguaje en SQL.
No aplica.

En cualquier caso, lo que preguntas es perfectamente posible... pero no se escribe con esos recursos, y la sintaxis puede cambiar según uses SQL Server, MySQL, Oracle u otro DBMS.
Oracle, por ejemplo, tiene consultas recursivas donde encontrar el árbol completo de relaciones padre/hijo de múltiples niveles, excluyendo al propio usuario, es una única query, de no más de cuatro o cinco lineas. En cambio MySQL no lo tiene, y hay que hacerlo de otra forma.

[skanskan]

gnzsoloyo, gracias por tu detalladísima explicación.
En realidad no pienso como un programador, de hecho no lo soy, sólo era una manera de explicarlo, para ser más concreto con un ejemplo.
Pues nada, a ver si practico un poco y ya os comento o incluso os pongo aquí mi código final.

Lo de las FK ya me ha quedado un poco más claro. Es que no sabía si las FK servían tamibén para algo así como "propagar" o linkar las consultas automáticamente, recurrencias automáticas, pero veo que no, que eso hay que especificarlo todo explicitamente y que las FK sólo sirven para construir las tablas y mejorar el rendimiento.

Y volviendo a mi ejemplo, que se me ocurrió viendo un video tutorial de youtube,...
para tener una relación many-to-many de padres, hijos, hermanos,etc. en realidad no hace falta tener 3 tablas, con una sola bastaría.

[gnzsoloyo]

Y volviendo a mi ejemplo, que se me ocurrió viendo un video tutorial de youtube,...
para tener una relación many-to-many de padres, hijos, hermanos,etc. en realidad no hace falta tener 3 tablas, con una sola bastaría.

En principio, si. Es lo usual hacer una única tabla, donde tienes una FK que apunta a la propia tabla, con la condición que debe ser nulable. Esto último debido a que no puedes referenciar eternamente todas las generaciones, sino que sería NULL aquel que corresponda a un padre/madre superior.
¿Se entiende?
Ese mismo esquema se usa para establecer la jerarquia de dependencias entre empleados, donde "jefe" es el que no depende de nadie.
En el caso de una estructura familiar, hay que recordar que puede existir padre y madre, por lo que podrían requerirse dos FK nulables apuntando a la misma tabla.

Nota: Ese esquema no sirve para establecer relaciones de amistad... Cuidado con eso. Se parecen, pero una red de relaciones requiere de al menos dos tablas.

[Libras]

Iniciado por skanskan

Y volviendo a mi ejemplo, que se me ocurrió viendo un video tutorial de youtube,...
para tener una relación many-to-many de padres, hijos, hermanos,etc. en realidad no hace falta tener 3 tablas, con una sola bastaría.

Claro con una sola basta, pero para fines de aprendizaje y como querias ver los datos usando FK era mejor usar 3 que no, si te hubiera puesto el ejemplo con una sola tabla hubieras entendido menos que lo que entendiste......

[skanskan]

Lo decía porque en algunos sitios dicen que las relaciones many-to-many se deben hacer con una tabla extra, pivote, pero veo que sólo es necesario a veces.

Y otra cosa.
Estaba leyendo eso que comentas de las autojoin, y mirando los ejemplos, junto a los que habéis puesto arriba.

Y veo que en ocasiones usáis
"FROM xxx JOIN yyy WHERE"
y a veces simplemente
"FROM xxx, yyy WHERE"
¿Qué ventaja tiene usar el JOIN?, ¿No es suficiente usar la separación por comas?
Entiendo que el JOIN me va a filtrar eliminando cosas pero así me parece más lioso que poniendo la condición en el Where o en el ON.

[gnzsoloyo]

Y veo que en ocasiones usáis
"FROM xxx JOIN yyy WHERE"
y a veces simplemente
"FROM xxx, yyy WHERE"
¿Qué ventaja tiene usar el JOIN?, ¿No es suficiente usar la separación por comas?
Entiendo que el JOIN me va a filtrar eliminando cosas pero así me parece más lioso que poniendo la condición en el Where o en el ON.

La coma es un JOIN implícito, que en algunos DBMS tiene como exigencia que los campos a relacionar tengan exactamente el mismo nombre en ambas tablas. El problema es que en ocasiones los programadores se olvidan de eso, y usan nombres iguales para datos diferentes, lo que termina generando resultados falsos.
Otra consecuencia es que si no hay campos del mismo nombre se producen productos cartesianos, lo que es un espanto para la performance.
Normalmente cuando se usa coma se indica la relación en el WHERE, pero en muchos DBMS el WHERE se procesa luego de leer las tablas basados en el JOIN implícito, lo que termina haciendo que se lea un conjunto de datos muy grande antes de tomar sólo lo que el WHERE determine.

El uso de JOIN / INNER JOIN, es lo que se denomina JOIN explícito y requiere para funcionar bien la cláusula ON, donde se indica la relación de las tablas

Código SQL:

SELECT a, b, c, d
FROM tabla1 T1 INNER JOIN tabla2 T2 ON T1.a_id = T2.a_id
 

En la mayoría de los DBMS, esa forma hace que el primer filtrado se realice a medida que los datos se leen, y antes de aplicar el WHERE, por lo que en realidad sólo lee los datos realmente útiles.
Es más eficiente en performance.

Todos los DBMS aceptan ambas formas (excepto algunas versiones viejas de Oracle), y la maoría de los tutoriales usan la coma porque toman sintaxis viejas y obsoletas, no por ser técnicamente inexactas.
Pero si quieres consultas eficiente, no uses coma. Usa JOINs explícitos.