I was not sure what to put in the title. I have a
SCHOOL table, with:
id, name, type_id
SCHOOL_STUDENT table with:
SCHOOL_TYPE table with
Students can be enrolled in more than one schools. A school can be of only one type. Given a student I want to check if he/she is enrolled in schools with only type(high shcool), and no other schools.I want to check if the student is enrolled in schools with only type(high shcool), and no other schools. If a student is enrolled in two schools and one of them is a high school and one of them is a technology school then I want some negative indication. If both of those schools are high school , i want positive indication. Let's suppose SCHOOL_TYPE has a row with id:3 and name:"high school".
SCHOOL table id:name:type_id
1:'abc school':3 2:'xyz school':4 3:'HEH school':3
SCHOOL_TYPE table id:name
1:'junior school' 2:'tech school' 3:'High school'
SCHOOL_STUDENT table: school_id:student_id
1,123 3,123 6, 56
See studentID:123 is enrolled in two schools, with id:1 and id:3. And both of those schools are of type:3, that means I want a success result. If the same student is enroled in more than one school and those schools belong to other types as well (other than typeID:3) then I want a failure.
Expected output for studentID:123 is YES and for studentID:56 is NO because he is enrolled in two schools and one of them is not a high school.
I am trying this but I know it lacks something but what it lacks, I don't seem to work it out.
select bc.customer_id, bt.name, count(bt.[name]) from school_type st join school s on s.type_id = st.id join school_student ss on ss.school_id = s.id where ss.student_id = 1234 group by ss.student_id, st.name
May be use HAVING? or CASE?