Exclude IDs in grouped query

[Ruegen]

I have a grouped query (max for some fields) and I would like to exclude some records that have bookings - I get the ids of the schools that have bookings from another query

I can't seem to get the query to exclude whatever id's are in the second query.



First grouped query is

SELECT Max(tblSchools.NewSchoolsID) AS GSchoolID, Max(tblSchools.SchoolName) AS GSchoolName, tblSchools.SchoolEmail AS GSchoolEmail, Max(tblSchools.[1ContactName]) AS G1ContactName, Max(tblSchools.[1ContactSurname]) AS G1ContactSurname
FROM (tblSchools LEFT JOIN tblTeacher ON tblSchools.NewSchoolsID = tblTeacher.NewSchoolsID) LEFT JOIN ((tblShows RIGHT JOIN (tblBookings RIGHT JOIN tblJncTeacher ON tblBookings.BookingsID = tblJncTeacher.BookingsID) ON tblShows.ShowsID = tblBookings.ShowsID) LEFT JOIN tblJncShows ON tblShows.ShowsID = tblJncShows.ShowsID) ON tblTeacher.TeacherID = tblJncTeacher.TeacherID
WHERE (((tblSchools.Removed) Is Null) AND ((tblSchools.AreaID)=67))
GROUP BY tblSchools.SchoolEmail;

The second (ungrouped) query is:

SELECT tblSchools.NewSchoolsID, tblBookings.BookingDate, tblJncShows.PerformersID
FROM (tblSchools INNER JOIN tblTeacher ON tblSchools.NewSchoolsID = tblTeacher.NewSchoolsID) INNER JOIN ((tblShows INNER JOIN (tblBookings INNER JOIN tblJncTeacher ON tblBookings.BookingsID = tblJncTeacher.BookingsID) ON tblShows.ShowsID = tblBookings.ShowsID) INNER JOIN tblJncShows ON tblShows.ShowsID = tblJncShows.ShowsID) ON tblTeacher.TeacherID = tblJncTeacher.TeacherID
WHERE (((tblBookings.BookingDate)>Date()) AND ((tblJncShows.PerformersID)=18));

Not sure as to how to build this

[John_G]

Hi -

Try your first query like this, with more criteria in the WHERE:

SELECT Max(tblSchools.NewSchoolsID) AS GSchoolID, Max(tblSchools.SchoolName) AS GSchoolName, tblSchools.SchoolEmail AS GSchoolEmail, Max(tblSchools.[1ContactName]) AS G1ContactName, Max(tblSchools.[1ContactSurname]) AS G1ContactSurname
FROM (tblSchools LEFT JOIN tblTeacher ON tblSchools.NewSchoolsID = tblTeacher.NewSchoolsID) LEFT JOIN ((tblShows RIGHT JOIN (tblBookings RIGHT JOIN tblJncTeacher ON tblBookings.BookingsID = tblJncTeacher.BookingsID) ON tblShows.ShowsID = tblBookings.ShowsID) LEFT JOIN tblJncShows ON tblShows.ShowsID = tblJncShows.ShowsID) ON tblTeacher.TeacherID = tblJncTeacher.TeacherID
WHERE (((tblSchools.Removed) Is Null) AND ((tblSchools.AreaID)=67)) And (tblSchools.NewSchoolsID not in (Select tblSchools.NewSchoolsID from secondqueryname))
GROUP BY tblSchools.SchoolEmail

Replace secondqueryname with the actual name of your second query.

This excludes all schools listed in the second query.

John

[Ruegen]

Hi -

Try your first query like this, with more criteria in the WHERE:

SELECT Max(tblSchools.NewSchoolsID) AS GSchoolID, Max(tblSchools.SchoolName) AS GSchoolName, tblSchools.SchoolEmail AS GSchoolEmail, Max(tblSchools.[1ContactName]) AS G1ContactName, Max(tblSchools.[1ContactSurname]) AS G1ContactSurname
FROM (tblSchools LEFT JOIN tblTeacher ON tblSchools.NewSchoolsID = tblTeacher.NewSchoolsID) LEFT JOIN ((tblShows RIGHT JOIN (tblBookings RIGHT JOIN tblJncTeacher ON tblBookings.BookingsID = tblJncTeacher.BookingsID) ON tblShows.ShowsID = tblBookings.ShowsID) LEFT JOIN tblJncShows ON tblShows.ShowsID = tblJncShows.ShowsID) ON tblTeacher.TeacherID = tblJncTeacher.TeacherID
WHERE (((tblSchools.Removed) Is Null) AND ((tblSchools.AreaID)=67)) And (tblSchools.NewSchoolsID not in (Select tblSchools.NewSchoolsID from secondqueryname))
GROUP BY tblSchools.SchoolEmail

Replace secondqueryname with the actual name of your second query.

This excludes all schools listed in the second query.

John

thanks John_G

That worked perfectly (and I get it too, so future wise I will not have the same issue)