table tution
institute | teacher | subject | students
--------------------------------------------
institute1 | john | maths | mary
institute1 | john | maths | stacy
institute2 | john | maths | david
institute2 | john | science | bruce
institute1 | tim | maths | steve
institute2 | tim | science | harry
institute1 | john | science | peter
each teacher in each subject should have limited students, suppose 25 students should be present for each subject in class.
So, here I want to write a single sql query to fetch the details of each teacher in a searched institute and their subject strength where institute is equal to searched institue.
which show only that teacher details which is not having strength of 25 students in each subject where institue is equal to searched institute.
I able to fetch the data but it will takes two queries and some php functions to get the result.
first getting the rows of searched institute and then after with the values of institute and with foreach loop and another count sql query built in php function i am creating an array with count values, but the problem is i am getting the result in an array two different arrays as institute teacher list and teacher count and then after i had to do a lot of work like merge and sort. So, that's why i am here to take some guidance from experts to resolve my problem.
I want to write a simple complete sql solution for this.
Hope someone here will help me out to get the result.
Thanks in Advance.