Help me to write an sql-statement to do something like this

Question

You can also write hibernate hql or criteria queries.

I have Teacher entity and Student entity like this :

class Teacher {
     public Long id ;
     public Set<Student> students;
} 

class Student {
    public Long id ;
    public Teacher teacher ;
    public Boolean passedSemester1;  
    public Boolean passedSemester2; 
}

You can assume my tables have the a the following structure.

Teacher and Student has a bidirectional one to many relationship. Student table manages the foreign key .

I need to find out the Teachers whose students all passed semester1 and semester2. Actually I also need to search :

all failed to pass both semester1 and semester2 , all passed semester1 but failed to pass semester2 , all failed to pass semester1 but passed semester2 .

You could write any one of this queries , others should be no big differences.

In order to not cause other misunderstandings , I abstract my real problem into this simple one. I need join the two tables to do other complex queries , so it's not so easy as just to query the students table.

Thanks !

Answer 1

In SQL, to find the teachers whose students all passed both semesters:

SELECT teacher_id
FROM student
GROUP BY teacher_id
HAVING MIN(passed_semester_1) AND MIN(passed_semester_2)

Results:

1
4

To find the teachers whose students all passed semester 1, but not all passed semester 2:

SELECT teacher_id
FROM student
GROUP BY teacher_id
HAVING MIN(passed_semester_1) AND NOT MIN(passed_semester_2)

Results:

2

---

Update join demonstration:

SELECT T2.*
FROM (
    SELECT teacher_id
    FROM student
    GROUP BY teacher_id
    HAVING MIN(passed_semester_1) AND MIN(passed_semester_2)
) AS T1
JOIN teacher AS T2 ON T1.teacher_id = T2.teacher_id

---

Using this table structure and test data:

CREATE TABLE student (student_id INT NOT NULL, teacher_id INT NOT NULL, passed_semester_1 INT NOT NULL, passed_semester_2 INT NOT NULL);
INSERT INTO student (student_id, teacher_id, passed_semester_1, passed_semester_2) VALUES
(1, 1, 1, 1),
(2, 1, 1, 1),
(3, 1, 1, 1),
(4, 2, 1, 1),
(5, 2, 1, 0),
(6, 2, 1, 1),
(7, 3, 0, 1),
(8, 3, 1, 1),
(9, 4, 1, 1);