I have the following query in MYSQL, where I search for all the students in each table for a particular semester.
If the student is entered into a table several times, it will only bring me the record with the lowest entry date.
So far so good, but if the student is entered in each table I will have it twice and what I need is only to know if it was in a semester and not have a student twice.
I hope someone can tell me how to do the query so that both tables consulted show only 1 student with the lowest date of entry.
SELECT
a.rut,
a.nombres,
a.apellidos,
DATE_FORMAT(MIN(a.fecha_ingreso), '%d-%m-%Y') as fecha_ingreso,
FROM form1 a INNER JOIN servicio s on s.id_form_1 = a.id_form_1
WHERE a.semestre_ano = $semestre and a.estatus = 1 GROUP BY 1,2,3
UNION
SELECT
b.rut,
b.nombres,
b.apellidos,
DATE_FORMAT(MIN(b.fecha_ingreso), '%d-%m-%Y') as fecha_ingreso,
FROM form2 b INNER JOIN departamento dep on dep.id_form_2 = b.id_form_2
WHERE b.semestre_ano = $semestre and b.estatus = 1 GROUP BY 1,2,3
The fastest way would be with subqueries: