How to get all students who do not owe fees to the date of the date

Question

How to get all students who do not owe fees to the date of the date

Navigation

#1 by (1 votes)

0

Good afternoon I have the following problem with an sql query: I need to get the name and surname of those students who are up to date with the payments of school fees.

My query is this but it does not work

SELECT CONCAT(pe.apellido," ",pe.nombre)AS alumno,pe.numero_documento,cu.* FROM persona AS pe
INNER JOIN alumno AS cl ON pe.id_persona=cl.id_persona
INNER JOIN inscripcion AS pr ON cl.id_persona=pr.id_persona
INNER JOIN cuota AS cu ON pr.id_inscripcion=cu.id_inscripcion
INNER JOIN estado_cuota AS ec ON cu.id_estado_cuota=ec.id_estado_cuota
GROUP BY pe.id_persona,cu.id_cuota,pr.id_inscripcion
HAVING (cu.id_estado_cuota=1 and (cu.id_estado_cuota!=3 AND cu.id_estado_cuota!=2)) AND cu.vencimiento < now()
ORDER BY pr.id_inscripcion ASC,cu.id_cuota ASC

For this I have the person table, the student table, the inscription table, the quota table and the quota table

id_estado_cuota=1 PAGADA
id_estado_cuota=2 IMPAGA
id_Estado_cuota=3 EN MORA

THE QUOTA TABLE CONTAINS THE FOLLOWING FIELDS

id_cuota int 
nro_cuota int
valor_cuota decimal
vencimiento date
id_estado_cuota int (clave foránea)
id_inscripcion int (clave foranea)

Could you please give me a hand?

Thank you very much. Greetings

mysql

asked by francisco castillo 10.10.2017 в 21:10

source

1 answer

ionic 3 browse pages sending href parameter How to join 2 Json in 1

score 1 · Accepted Answer

Ok, first of all, you should understand the logic to find the quotas that are not up to date (which I suppose are those whose due date is earlier than today, and whose status is not "Paid"). This would be the query to find those quotas:

SELECT *
FROM Cuota 
WHERE vencimiento < NOW()
AND id_estado_cuota <> 1;

Now we can mix this query with the rest of the tables to find the data you need:

SELECT  CONCAT(pe.apellido," ",pe.nombre) AS alumno,
        pe.numero_documento
FROM persona AS pe
INNER JOIN alumno AS cl 
    ON pe.id_persona = cl.id_persona
INNER JOIN inscripcion AS pr 
    ON cl.id_persona = pr.id_persona
WHERE pr.id_inscripcion NOT IN (SELECT DISTINCT id_inscripcion 
                                FROM Cuota 
                                WHERE vencimiento < NOW()
                                AND id_estado_cuota <> 1);