Consultas a relaciones many-to-many, en Access ?

[skanskan]

Buenas.

Hace tiempo que ando pensando sobre las relaciones many-to-many.
Sé que se hacen usando una tabla intermedia, llamada pivot, joint o link table, que tendrá relaciones many-to-1...
Mi duda es como hacer luego las consultas.

He inentado hacerlo primero en Access
Siguiendo un tutorial de youtube
https://www.youtube.com/watch?v=WtEj_Fb6eaw

he creado un ejemplo simple, una tabla de nombres, una de coches, y otra que relaciona los coches que tiene cada cual.

En ese video la consulta la hacen simplemente pidiendo un campo de una tabla y otro de otra.
Se puede ver entonces que la SQL queda como
SELECT personas, coches FROM ....

el problema es que esa consulta no me genera el resultado que quiero, no me dice que coches tiene cada persona sino que
me da todas las posibles combinaciones, sin tener en cuenta para nada las relaciones.

He adjuntado el archivo de ejemplo que he creado.
http://bitshare.com/files/m2w390sq/Database2.accdb.html
¿Cómo debo hacer la consula en Access para obtener lo correcto?
Si es posible sin hacer referencia de momento al código SQL sino a la forma tradicional en la que se hace esto en Acess, con criterios y con el interfaz gráfico...





y el resultado que obtengo:

[skanskan]

Se me ocurre que si fuera en SQL, aunque tampoco tengo ni idea, habría que añadir un

Código:

WHERE id.personas = persona.pivot AND id.coches = coches.pivot

y quizá alguna condición más, o usar un INNER JOIN con alguna condición en el ON.
Pero eso no sé si tiene algún equivalente simple en Access para que haga uso automático de las relaciones

[Libras]

es casi lo mismo que habias preguntado, jugando un poco con los join, distinct, funciones de agregacion y demas puedes obtener lo que necesitas....

[skanskan]

¿Y no se puede hacer sin escribir código SQL?

[gnzsoloyo]

Programaticamente, pero el resultado y performance serían un asco.
¿Por qué crees que se inventó el SQL y se crearon los DBMS?

[skanskan]

Ostras, que tontería,
ya he visto mi fallo.
Al final sí que se puede hacer con Access sin necesidad de escribir SQL.

Al definir el Query he utilizado un campo de una tabla y otro campo de otra
y me faltaba poner poner en esa pantalla la tabla pivote, aunque luego no uso sus campos directamente.

Mágicamente al poner las tres tablas aparece todo bien, sin necesidad de criterios, uniones ni nada.

Lo que me mosquea es que no se puede elegir como hace el query el Access.

[gnzsoloyo]

Porque Access no es un sistema de bases de datos, sino un aplicativo de manejo de tablas con recursos de SQL agregados.
El verdadero DBMS de Microsoft es SQL Ser ver, no Access.

[skanskan]

¿Y la consulta más simple o eficiente para hacer esto en SQL?

[Libras]

ejemplo de tus datos y con mucho gusto :)

[skanskan]

Cualquier ejemplo me sirve pero te pongo los datos (tres tablas) de mi ejemplo

Código:

Coches
------
ID	Modelo
1	Ford
2	Seat
3	Mercedes
4	BMW

Código:

Personas
---------
ID	Nombre
1	Ana
2	Carlos
3	Dani
4	Jose
5	Maria
6	Vicente

Código:

Pivot
-----
ID	Personas	Coches
1	1	1
2	1	2
3	2	3
4	3	2
5	4	4

También he visto que está última, la pivot table, hay quien la hace con sólo dos columnas, y creando un indice conjunto para evitar repeticiones.

El resultado que hay que obtener es:

Código:

Modelo	Nombre
Ford		Ana
Seat		Ana
Mercedes 	Carlos
Seat		Dani
BMW		Jose

que yo he obtenido creando relaciones many-to-1 desde la pivot table hasta las ID de las otras dos.
a lo mejor estoy complicándolo más de lo necesario.

Otra cosa, ¿Cómo haces para convertir esas tablas de datos de Access directamente en sentencias SQL de creación de tablas?
Porque para eso no está activa la opción "ver como SQL". y un copy&paste no sirve.

saludos


PD: parece que el Acces al tener seleccionada la tabla pivote (además de las otras) esa consulta la hace como

Código SQL:

SELECT Coches.Modelo, Personas.Nombre
FROM Personas INNER JOIN (Coches INNER JOIN [Pivot] ON Coches.ID = Pivot.Coches) ON Personas.ID = Pivot.Personas;
 

no sé si se podría hacer con Where en vez de Joins con ON, ni sé que es más eficiente.

¿Por qué ese [Pivot] se pone entre corchetes y lo demás no?
Y como conclusión de todo esto, parece que en SQL Server el simple hecho de tener definidas relaciones FK no te afecta a las consultas, tienes que especificarlo todo a mano, pero en Access sí, lo hace todo bastante automático.

[Libras]

Lo que pasa es que Access es una herramienta para que un usuario "comun" pueda hacer informes sin necesidad de saber nada de bases de datos, por eso hace "tanta" maravilla, ahora sql server es un sistema especializado en bases de datos por lo que las consultas las tienes que hacer a mano no es automatico, si no porque crees que los sueldos de un experto de access son menores a los de un experto de sql ;)

[skanskan]

¿alguien sabe por qué ese [Pivot] se pone entre corchetes y lo demás no?

[jchuk]

Hola,

Pivot es una palabra reservada de access(Pivot Table), por eso la pone entre corchetes, para identificar que se refiere a una tabla de la BD. El resto de veces que aparece va acompañada del operador "." por lo que es evidente que no hace referencia a una palabra reservada sino que es la sintaxis expresada para refererirse a un campo concreto de una tabla.

Saludos!!