how to bring a record of a table if and only if a state is fulfilled in records of another table refererda to the id of the first?

0

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 Economy

     

correlatives
  id ids idCo
  1__3__1
  2__3__2
  3__4__2
  4__4__3

     

history
  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.

    
asked by user9433543 22.05.2018 в 06:26
source

0 answers