tengo 2 tablas mysql relacionadas de una a muchos y quiero que aparezcan en una consulta los registros que coinciden en la tabla muchos con 2 caract

Question

Buenas espero me de a entender, tengo una tabla con una relación a otra de una a muchos, quisiera mostrar los registros de la primer tabla, que aparece en 2 instancias en la segunda tabla, por ejemplo, la primera tiene a un maestro con su id, y en la segunda aparece el maestro en 2 ocasiones, por que la segunda tiene las carreras que han estudiado estos maestros, todo bien cuando busco los maestros que estudiaron una carrera, pero cuando busco los que han estudiado ingeniería y psicología por ejemplo aparece un resultado vacío, pero si hay en los registros maestros que estudiaron esas 2 carreras y si las consulto por separado sí aparecen , pero cuando digo que estudiaron ambas ya no aparece, en la imagen lo explico gráficamente , gracias

Answer 1

No es que hayas perdido la práctica, es que este problema es más complejo de lo que parece, porque tu consulta pide un imposible: una carrera no puede ser ingeniería y psicología simultáneamente, que es lo que pides en tu consulta, en vez de pedir aquellos maestros que hayan cursado ambas carreras, que se pide de otra forma.

Para entenderlo tienes que ver qué le queda a tu motor de la base de datos tras el JOIN para procesar el WHERE tu consulta:

+------+--------+------+------------+-----------+-------------+
| id   | nombre | rfc  | turno      | idmaestro | carrera     |
+------+--------+------+------------+-----------+-------------+
|    1 | Mateo  | abc  | matutino   |         1 | ingeniería  |
|    1 | Mateo  | abc  | matutino   |         1 | psicología  |
|    2 | Juan   | def  | vespertino |         2 | ingeniería  |
+------+--------+------+------------+-----------+-------------+

Presentado así, verás que un mismo registro no puede tener dos carreras, es imposible, porque lo que tendrás serán 2 registros, y un WHERE opera registro a registro, no por conjuntos.

Por eso esta consulta dará necesariamente un conjunto vacío:

SELECT * FROM documentacion
  JOIN carreras ON id=idmaestro
  WHERE carrera='ingeniería'
  AND carrera='psicología';

Tendrías que partir de esta otra:

SELECT * FROM documentacion
  JOIN carreras ON id=idmaestro
  WHERE carrera='ingeniería'
  OR carrera='psicología';

Y entonces asegurarte de que, agrupando por maestro, tengan estas dos titulaciones. Que se consigue así:

SELECT id,nombre
  FROM documentacion
  JOIN carreras ON id=idmaestro
  WHERE carrera='ingeniería'
  OR carrera='psicología'
  GROUP BY 1,2
  HAVING COUNT(*)=2;

Obteniendo:

+------+--------+
| id   | nombre |
+------+--------+
|    1 | Mateo  |
+------+--------+

Para el dataset:

CREATE TABLE documentacion(
  id int,
  nombre varchar(60),
  rfc varchar(36),
  turno set('matutino','vespertino','nocturno')
  );
CREATE TABLE carreras(
  idmaestro int,
  carrera set('ingeniería','psicología','leyes','filosofia')
  );
INSERT INTO documentacion (id, nombre, rfc, turno) VALUES
  (1,'Mateo','abc','matutino'),
  (2,'Juan','def','vespertino');
INSERT INTO carreras (idmaestro, carrera) VALUES
  (1,'ingeniería'),
  (1,'psicología'),
  (2,'ingeniería');

Espero que lo veas de una forma un poco más clara. Déjame cualquier duda en los comentarios, no uses imágenes para tus consultas y pega en modo texto el código de tus intentos.

ChatGPT, que es mucho más gallo, lo soluciona haciendo una intersección de conjuntos usando dos JOIN y tengo que reconocer que es una solución mucho más limpia y elegante. Así que también te la dejo, aunque reconozco que no creo que se me hubiera ocurrido. Tal vez a base de resolver muchas así...

SELECT d.id, d.nombre, d.rfc, d.turno
  FROM documentacion d
  JOIN carreras c1 ON d.id = c1.idmaestro AND c1.carrera = 'ingeniería'
  JOIN carreras c2 ON d.id = c2.idmaestro AND c2.carrera = 'psicología';