What I seek to obtain is that if the subject has correlatives, that all are in state 1, approved, and if it has any not approved or is not in the record that does not bring the subject. I have three tables:
subject (id, description)
correlatives (id, idAsignatura, idCorrelativas)
history (id, idAsignatura, estado)
I clarify that the correlative, means that the right to render the current one depends on the approval of its correlatives. Each subject can have one, several, or none correlative.
Currently I'm looking for the id of the subject, with this I look for their corresponding correlatives and with them I look in the student's history if they have it approved.
My problem appears when I try to bring the matter only if it has all correlatives in state 1, which would be approved. But when doing the query, it brings me the subject if it has only one of the approved correlatives although, for example, it has two or more. My query is:
SELECT as.*
FROM asignatura as, correlativas co, historial hi
WHERE as.id = co.idAsignatura
AND co.idCorrelativa = hi.idAsignatura
AND hi.estado = 1
subject
id description
1 Mathematics
2 Mathematics II
3 Accounting
4 Economycorrelatives
id ids idCo
1__3__1
2__3__2
3__4__2
4__4__3history
id idAs status
1__1__1
2__2__1
3__3__0
The expected result is to get only Accounting.
Accounting and Economics are obtained, since the latter has an approved correlative of the 2 assigned to it.
Greetings and thanks.