2

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:

  • 1
    I am not really sure what your question is - however if your query is only on the issue of count, you may try "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 having count(bt.[name]) = 1" and let us know. – Learning_DBAdmin Feb 26 '19 at 6:46
  • @Learning_DBAdmin in short you are suggesting to add having count(bt.[name]) = 1 at the end of my query. If yes, then no, it will not work as it will group and give all the types of shcool in which student is enrolled and school is only associate with one type. – user1451111 Feb 26 '19 at 6:50
  • 1
    @Learning_DBAdmin I have updated the question slightly to make it more understandable. – user1451111 Feb 26 '19 at 6:52
  • @SabinBio added sample data. – user1451111 Feb 26 '19 at 7:04
  • You need to return st.name? can be removed from select,group by ? – Sabin B Feb 26 '19 at 8:05
1

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

  • 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. – user1451111 Feb 26 '19 at 7:57
  • i have updated the question with expected output. – user1451111 Feb 26 '19 at 8:06
0

I would group the students and divide id sum with the count. This division should give the id if all school id's are the same, for example: (3+3)/2 = 3.

So the query would go like this:

SELECT
    ss.student_id,
    CASE
        WHEN sum(s.id)/count(s.id) = 3 THEN 'YES'
        ELSE 'NO'
    END AS result
FROM
    school_type st 
    JOIN school s on s.type_id = st.id
    JOIN school_student ss on ss.school_id = s.id
GROUP BY ss.student_id
ORDER BY ss.student_id;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.