Consulta a una tabla con relación M:M

[luis_h_1]

Hola, tengo un problema con una consulta.

Tengo 3 tablas: materia, profesor, materia_profesor

materia tiene 2 campos: id_materia, nombre_materia
profesor tiene 2 campos: id_profesor, nombre_profesor
y materia_profesor 2 campos: id_materia, id_profesor, ambas forman una Primary key

La tabla "materia_profesor" la ocupo para asignarle materias a los profesores, tengo un formulario donde despliego una lista con todos los profesores, y quiero que me despliegue un listado de materias que ese profesor no tenga asignadas, por ejemplo:

profesor: 1.- jose, 2.- mario

materia: 1.- matemáticas, 2.- biología, 3.- programación, 4.- química

profesor 1 tiene asignadas las materias 1, 2

en la tabla materia_profesor estaria como 1:1,1:2

ya lo he intentado de varias formas pero no me funciona, ojala puedan ayudarme, saludos.

[leonardo_josue]

Hola Luis_h_1

En tu post mencionas:

ya lo he intentado de varias formas pero no me funciona

Sin embargo no nos dices qué es lo que intentaste hacer, ¿si las consultas que hiciste marcaron algún error? pues debes decirnos cuál es ese error que marca. Si las consultas no te regresan los resultados esperados, debes decirnos qué es lo que obtienes como resultado, pero SIEMPRE INCLUIR ALGO DE LO QUE HAZ INTENTADO HACER.

Opciones, tienes muchas, puedes hacerlo con una condición NOT EXISTS, o puedes hacer un NOT IN y una subconsulta donde obtengas las materias que SI TENGA ASIGNADAS el profesor... finalmente puedes hacer un LEFT JOIN entre tus tablas materia y materia_profesor, filtrando SÓLO AQUELLOS QUE TENGAN NULL en los campos materia_profesor...

Sería muy fácil simplemente copiar el código aquí con las respuestas, pero me gustaría que lo intentaras... cualquier problema que tengas lo comentas en el foro para tratar de ayudarte.

Saludos
Leo.

[luis_h_1]

Gracias por contestar leonardo_josue, una disculpa por no haber puesto todo los intentos que habia realizado... pero gracias al tip que me dijiste pude resolverlo, aqui va la solución:


SELECT * FROM materia
WHERE id_materia NOT IN (SELECT id_materia FROM materia_profesor WHERE id_profesor=$profesor)

donde $profesor es una variable.

Saludos y gracias

[leonardo_josue]

Hola de nuevo:

Me alegra mucho que hayas podido encontrar una respuesta por tu propia cuenta... tomo te mencioné en el post, hay varias formas de obtener la misma respuesta... además de NOT IN... checa este script:

Código MySQL:

mysql> select * from materia;
+------------+----------------+
| id_materia | nombre_materia |
+------------+----------------+
|          1 | matemáticas    |
|          2 | biología       |
|          3 | programación   |
|          4 | química        |
+------------+----------------+
4 rows in set (0.01 sec)
 
mysql> select * from profesor;
+-------------+-----------------+
| id_profesor | nombre_profesor |
+-------------+-----------------+
|           1 | josé            |
|           2 | mario           |
+-------------+-----------------+
2 rows in set (0.02 sec)
 
mysql> select * from materia_profesor;
+------------+-------------+
| id_materia | id_profesor |
+------------+-------------+
|          1 |           1 |
|          2 |           1 |
+------------+-------------+
6 rows in set (0.00 sec)
 
mysql> #utilizando NOT IN
mysql> SELECT * FROM materia M
    -> WHERE id_materia NOT IN (
    -> SELECT id_materia FROM materia_profesor MP
    -> WHERE MP.id_profesor = 1);
+------------+----------------+
| id_materia | nombre_materia |
+------------+----------------+
|          3 | programación   |
|          4 | química        |
+------------+----------------+
2 rows in set (0.00 sec)
 
mysql> #utilizando NOT EXISTS
mysql> SELECT * FROM materia M WHERE NOT EXISTS (
    -> SELECT * FROM materia_profesor MP
    -> WHERE MP.id_materia = M.id_materia AND MP.id_profesor = 1);
+------------+----------------+
| id_materia | nombre_materia |
+------------+----------------+
|          3 | programación   |
|          4 | química        |
+------------+----------------+
2 rows in set (0.00 sec)
 
mysql> #utilizando LEFT JOIN
mysql> SELECT M.* FROM materia M
    -> LEFT JOIN materia_profesor MP
    -> ON MP.id_materia = M.id_materia AND MP.id_profesor = 1
    -> WHERE MP.id_materia IS NULL;
+------------+----------------+
| id_materia | nombre_materia |
+------------+----------------+
|          3 | programación   |
|          4 | química        |
+------------+----------------+
2 rows in set (0.00 sec)
 

En lo particular prefiero usar NOT EXISTS en lugar de NOT IN, pues te permite hacer comparaciones con n campos, y no solo con uno, como en el caso de NOT IN... sería conveniente también que revisaras los tiempos de respuesta entre las distintas opciones, para elegir aquella con mejor desempeño.

Saludos
Leo.

[luis_h_1]

Gracias por la ayuda, no utilicé el LEFT JOIN porque aún no me queda muy claro como es que funciona, incluyendo el RIGHT JOIN, he leído algunos tutoriales, pero no me queda claro, seguiré buscando más...

Saludos.

[leonardo_josue]

Hola de nuevo luis_h_1:

Iniciado por luis_h_1

Gracias por la ayuda, no utilicé el LEFT JOIN porque aún no me queda muy claro como es que funciona, incluyendo el RIGHT JOIN, he leído algunos tutoriales, pero no me queda claro, seguiré buscando más...

Como he habrás dado cuenta, los JOIN's te sirven para unir dos tablas relacionadas por uno o más campos, la cardinalidad del INNER JOIN es 1 a n, es decir un registro de la tabla A DEBE TENER AL MENOS UN REGISTRO EN LA TABLA B, si no existe este registro entonces NO SE MUESTRA en el resultado final. EL LEFT JOIN tiene una cardinalidad de 1 a 0 o más, es decir, TODOS LOS REGISTROS DE LA TABLA A se muestran, si existen registros en la tabla B que estén relacionados también se muestran, pero SI NO EXISTEN REGISTROS ENTONCES LOS CAMPOS SE PONEN COMO NULL, el RIGHT JOIN es semejante, pero cambia el orden de las tablas, es decir, los SE MUESTRAN TODOS LOS REGISTROS DE LA TABLA B, y solo los registros de la tabla A que se relacionen. Checa este script:

Código MySQL:

mysql> SELECT * FROM materia;
+------------+----------------+
| id_materia | nombre_materia |
+------------+----------------+
|          1 | matemáticas    |
|          2 | biología       |
|          3 | programación   |
|          4 | química        |
+------------+----------------+
4 rows in set (0.00 sec)
 
mysql> SELECT * FROM materia_profesor;
+------------+-------------+
| id_materia | id_profesor |
+------------+-------------+
|          1 |           1 |
|          2 |           1 |
+------------+-------------+
2 rows in set (0.00 sec)
 
mysql> SELECT * FROM materia M
    -> LEFT JOIN materia_profesor MP
    -> ON MP.id_materia = M.id_materia AND MP.id_profesor = 1;
+------------+----------------+------------+-------------+
| id_materia | nombre_materia | id_materia | id_profesor |
+------------+----------------+------------+-------------+
|          1 | matemáticas    |          1 |           1 |
|          2 | biología       |          2 |           1 |
|          3 | programación   |       NULL |        NULL |
|          4 | química        |       NULL |        NULL |
+------------+----------------+------------+-------------+
4 rows in set (0.00 sec)
 

Observa que los id 3 y 4 de la tabla A (materia) NO EXISTEN EN LA TABLA B (materia_profesor), por lo tanto los campos id_materia y id_profesor que corresponden a esta tabla se muestran como NULL para estos registros.

checa ahora el uso del INNER JOIN:

Código MySQL:

mysql> SELECT * FROM materia M
    -> INNER JOIN materia_profesor MP
    -> ON MP.id_materia = M.id_materia AND MP.id_profesor = 1;
+------------+----------------+------------+-------------+
| id_materia | nombre_materia | id_materia | id_profesor |
+------------+----------------+------------+-------------+
|          1 | matemáticas    |          1 |           1 |
|          2 | biología       |          2 |           1 |
+------------+----------------+------------+-------------+
2 rows in set (0.00 sec)
 

Observa ahora que como los id 3 y 4 de la tabla A NO EXISTEN EN LA TABLA B entonces NO SE MUESTRAN EN EL RESULTADO FINAL

Checa también lo que nos dice WIKIPEDIA acerca de los JOIN's

http://es.wikipedia.org/wiki/Join

En lo particular creo que es bastante clara la explicación que da acerca de cómo se comportan cada uno de los JOIN's. Espero que con esto te quede un poco más claro el funcionamiento de los JOIN's, pues te aseguro que te servirán muchísimo al momento de desarrollar tus consultas.

Saludos
Leo.