fetch teacher and subject wise details in mysql and php

[bhrmalik]

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.

[kickstart]

Hi

 

Not 100% sure I know what you are trying to find.

 

I think you are trying to get all the teachers for a specified institute where the classes they take don't have 25 students

 

If so:-

 

SELECT institute, teacher, subject, count(students) AS StudentCount
FROM tuition
WHERE institute = 'someinstitute'
GROUP BY institute, teacher, subject
HAVING StudentCount < 25

 

All the best

 

Keith

[bhrmalik]

Thanks kickstart it is almost what I was fetching for. But I need one more query I will post another post for that.

As for this query it is solved.

I had asked in Stack Overflow and another sites also but got the solution in here, by you kickstart. Thank you once again.

Thanks you very much.

[fenway]

I had asked in Stack Overflow and another sites also but got the solution in here, by you kickstart.

If you're going to cross-post to different boards, please make sure that you post the solution here no matter what -- please don't just troll for answers and then run away.