Consulta correlacionada?

[SuperAl1]

Buenas a todos, tengo un problema y es que no me sale una consulta y no se si es que tengo que hacer consultas correlacionadas o no se... a ver os doy los datos:

Tengo una tabla actividades:
id, .... ,... ,.... fecha_inicio, fecha_fin

una tabla actividades_alumnos (intermedia)
id, actividad_id, alumno_id

una tabla alumnos
id, nombre, apellidos......

En la tabla actividades_alumnos controlo qué alumnos estan apuntados a que actividad (N:N).

La pregunta es... consulta que me muestre los alumnos que estan en conflicto por estar en dos o mas actividades cuyas fechas se crucen, mostrando el alumno y las actividades para las que hay conflicto.

Es lógico pensar que una actividad cruzada con otra es que si una actividad empieza el 1 de febrero y termina el 15... haya otra que empiece el 7 de febrero y termine el 20. Así si el alumno esta matriculado en las 2, se tiene que mostrar porque materialmente sería imposible.

Estoy dandole vueltas pero no doy con la solución.... ¿Alguna idea?

[kakoxvid]

tal vez deberias validar al momento de ingresar los datos por ejemplo al momento de ingresar datos en la tabla actividades_alumnos podrias preguntar si el alumno se encuentra y si es asi validar que la fecha_inicio que estas ingresando sea mayor a la fecha_fin de la que ya esta almacenada y asi te evitas mayores problemas

[SuperAl1]

El problema es que ya estan todos insertados (30.000 registros) y ahora me haría falta esa consulta para que al usuario administrador le saliese el mensaje de que hay varios alumnos en ese conflicto

[sanchy]

vuelvo a insistir, como en otro topic. por q hacerlo con la consulta? por q no agarrar toda la tabla, asignarsela a una variable, y despues si, con la variable haces lo que quieras. me parece mucho mas facil

[jurena]

Probablemente sanchy tenga razón y con programación también podrías hacerlo, pero estamos en un foro de base de datos y te daré una respuesta de base de datos, en una consulta, aunque he de añadir que no sé si será más eficiente que hacer un par de consultas y utilizar algo de programación:

Código sql:

SELECT a.nombre, a.apellidos, ac.id, ac.actividad, ac.fecha_inicio, ac.fecha_fin
FROM alumnos a
INNER JOIN (
 
SELECT aa.alumno_id ID, t1.id1 ID1, t1.id2 ID2
FROM actividades_alumnos aa
INNER JOIN (
 
SELECT a.id id1, b.id id2
FROM actividades a
INNER JOIN actividades b
WHERE (
a.id <> b.id
)
AND (
(
b.fecha_inicio >= a.fecha_inicio
AND b.fecha_inicio <= a.fecha_fin
)
OR (
b.fecha_inicio <= a.fecha_inicio
AND b.fecha_fin > a.fecha_inicio
)
)
)t1 ON aa.actividad_id = t1.id1
OR aa.actividad_id = t1.id2
GROUP BY aa.alumno_id
HAVING COUNT( * ) >2
)t2 ON a.id = t2.ID
INNER JOIN actividades ac ON ac.id = t2.ID1
OR ac.id = t2.ID2
ORDER BY a.id, ac.fecha_inicio, ac.fecha_fin