Sql-server – query to return only one kind of data

sql server

I was not sure what to put in the title. I have a
SCHOOL table, with: 

id, 
name,
type_id

columns.

A SCHOOL_STUDENT table with:

school_id,
student_id

columns

and a SCHOOL_TYPE table with

id, 
name

columns.

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

SAMPLE DATA:

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?

Question UPDATED:

Best Answer

I will try to answer to you; it's not fully clear to me , but anyway , here is my first version

create table SCHOOL (
     id int, 
     name varchar(30),
     type_id int
)

create table  SCHOOL_STUDENT(
     school_id int,
     student_id int
)
create table  SCHOOL_TYPE (
   id int,
   name varchar(30)
)

insert into SCHOOL(id, name , type_id)
values(1,'abc school',3) 
       ,(2,'xyz school',4)
       ,(3,'HEH school',3);

insert into SCHOOL_STUDENT (school_id, student_id)
values(1,123),(3,123),(2,56);

insert into  SCHOOL_TYPE (id,name)
values (1,'junior school'),
       (2,'tech school'),
       (3,'High school'),
       (4,'Ultra High school');

Here is my query:

select
   ss.student_id
   ,case when max(isHighSchool) = 1 
         and min(isHighSchool) = 1 then 'YES' else 'NO' end as isHighSchool
from
(
    select
        ss.student_id--, st.name
        ,case when st.name = 'High school' then 1 else 0 end as isHighSchool
    from
         school_type st 
         inner join school s on s.type_id = st.id
         inner join school_student ss on ss.school_id = s.id
)ss
group by ss.student_id

ouput:

student_id  isHighSchool
56          NO
123         YES

dbfiddle here

Related Question