Query to find schools with only boy/girls

[retrib]

I've been struggling with this one for quite some time now and would appreciate some help:-

I'm trying to get a list of schools that only has male students. the closest I have so far:-


SELECT Schools.SchoolName, FORUM_MEMBERS.M_SEX , COUNT(Schools.SchoolName) AS no_of_students
FROM Schools
INNER JOIN FORUM_MEMBERS ON (Schools.SchoolID = FORUM_MEMBERS.SchoolID)
WHERE (FORUM_MEMBERS.UserType=4)
GROUP BY Schools.SchoolName, FORUM_MEMBERS.M_SEX
ORDER BY Schools.SchoolName, FORUM_MEMBERS.M_SEX;

The result is a list of schools:-
SchoolName, M_SEX, no_of_students
school 1, Male, 11
school 1, Female, 20
school 2, Male, 8
school 2, Female, 1
school 3, Male, 12
school 4, Male, 14
school 5, Male, 8
school 5, Female, 1

But what I really need is schools with only male students:-
SchoolName, M_SEX, no_of_students
school 3, Male, 12
school 4, Male, 14

Many Thanks

[mxhxr]

Perhaps you could use a correlated subquery; join to the FORUM_MEMBERS table twice, once for Male, once for Female. You'll then have School Name, Male Count, and Female Count in a single row. From there, you could only return those where Female = 0.

[nosepicker]

Try this:

SELECT Schools.SchoolName, FORUM_MEMBERS.M_SEX , COUNT(Schools.SchoolName) AS no_of_students
FROM Schools
INNER JOIN FORUM_MEMBERS (ON Schools.SchoolID = FORUM_MEMBERS.SchoolID)
WHERE (FORUM_MEMBERS.UserType = 4)
AND NOT EXISTS (SELECT 1 FROM FORUM_MEMBERS AS FORUM_MEMBERS2 WHERE M_SEX = 'Female' AND FORUM_MEMBERS.SchoolID = FORUM_MEMBERS2.SchoolID)
GROUP BY Schools.SchoolName, FORUM_MEMBERS.M_SEX
ORDER BY Schools.SchoolName, FORUM_MEMBERS.M_SEX;

[shamshe]

You are just one step close:
SELECT Schools.SchoolName, FORUM_MEMBERS.M_SEX , COUNT(Schools.SchoolName) AS no_of_students
FROM Schools
INNER JOIN FORUM_MEMBERS ON (Schools.SchoolID = FORUM_MEMBERS.SchoolID)
WHERE (FORUM_MEMBERS.UserType=4) AND (FORUM_MEMBERS.M_SEX='Male')
GROUP BY Schools.SchoolName, FORUM_MEMBERS.M_SEX
ORDER BY Schools.SchoolName, FORUM_MEMBERS.M_SEX;

[nosepicker]

Shamshe, no. The original poster wanted the schools with ONLY male students. Your query brings back ANY schools with male students, including those with both male and female students. Use test data to see this more clearly.

[shamshe]

I see now, apparently didn't read carefully.

thanks for correcting

[retrib]

Thanks all, I've tried the ablove but I've still not sussed it yet though. I think I'm going to have to out source this one.

[mikr0s]

SELECT Schools.SchoolName, tmp.males, tmp.females
FROM Schools
JOIN
--following subquery returns distribution of students per school
(
SELECT SchoolID , COUNT(CASE M_SEX WHEN 'Male' THEN 1 END) AS males, COUNT(CASE M_SEX WHEN 'Female' THEN 1 END) AS females
FROM FORUM_MEMBERS
GROUP BY SchoolID) tmp ON tmp.SchoolID = Schools.SchoolID
WHERE tmp.females = 0

--HTH