Re: Help with Join Query

[Rob James]

I've tried nesting one of the selects like this:

SELECT
AgeRangeNames.ID
, COUNT(Students.AgeRange) AS Total
FROM
AgeRangeNames
LEFT JOIN Students
ON AgeRangeNames.ID = Students.AgeRange
LEFT JOIN (SELECT StudentID FROM Registrations
WHERE Registrations.Year = "10"
AND Registrations.Status = "1"
AND Registrations.ClassID ="58") as x1
ON x1.StudentID = Students.ID
GROUP BY
AgeRangeNames.ID

But it is still giving me all the students, ie.

F1 1
F2 1
F3 1
F4 0
F5 1
M1 1
M2 1
M3 3
M4 0
M5 1

and not just the ones for the class, year & status that I want.

I'm really stumped on this... what I want to do is to do the join with the students and registrations tables to get a list of valid students, and THEN join that with the ageRangeNames table to get a list of all age ranges and the number of students in each range (or 0 if there are none) but I am having trouble figuring out how to write the MYSQL... any suggestions or hints would be appreciated!

The AgeRangeName table looks like this:

ID Gender Range
F1 F 16-24
F2 F 25-34
F3 F 35-44
F4 F 45-54
F5 F 55+
M1 M 16-24
M2 M 25-34
M3 M 35-44
M4 M 45-54
M5 M 55+

The Registrations table looks like this:

ClassID StudentID Status Year
58 5 1 10
58 6 1 10
58 8 1 10
58 9 1 10
58 10 1 10
58 11 1 10
58 12 1 10
58 13 1 10
59 7 1 10

A student like this, some data omitted (address, city, etc) for simplicity:

ID 5
FN John
LN Doe
Age 39
AgeRange M3