query

[Steve]

Hi All

I may be trying to do something that is not possible to do, but here goes:

I am working with three tables. The first one, called students, contains a
list of all students. The second one, called sport, contains a list of
several sports the students could sign up for. A third table, called
studSport show those students that have signed up for a sport by their
unique ID's. and the sport's unique id. For instance, if the student's id is
1, and the sport's id from the sports table is 3, this data would be stored
in the studSport table as 1 and 3.

I need to be able to show a list of all students that have NOT signed up for
a specific sport. For instance, if football season is upon us, I would want
to be able to show all students who have not signed up for football. The
syntax for this kind of query is completely eluding me, so any help would be
very much appreciated.

Steve

 

[John Spencer]

THe basic concept is to identify all students that HAVE signed up for the
sport and then use that in an unmatched query to identify those that have
not.

SELECT Students.*
FROM Students
WHERE StudentID Not In
(SELECT StudentID
FROM StudSport
WHERE SportID = 3)

The problem with the above is the Not In clauses are slow. IF performance
for the above is not sufficient, then the following is much faster but can
only be done in one query if your table and field names following the naming
conventions.
(Only letters, numbers, and underscore characters used in the names and no
reserved words such as "date" as a name)

SELECT Students.*
FROM Students LEFT JOIN
(SELECT StudentID
FROM StudSport
WHERE SportID = 3) as Footballers
ON Students.StudentID = Footballers.StudentID
WHERE Footballers.StudentID is Null
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Steve]

Thank you John, I'll give these a try.

Steve

THe basic concept is to identify all students that HAVE signed up for the
sport and then use that in an unmatched query to identify those that have
not.

SELECT Students.*
FROM Students
WHERE StudentID Not In
(SELECT StudentID
FROM StudSport
WHERE SportID = 3)

The problem with the above is the Not In clauses are slow. IF performance
for the above is not sufficient, then the following is much faster but can
only be done in one query if your table and field names following the
naming conventions.
(Only letters, numbers, and underscore characters used in the names and no
reserved words such as "date" as a name)

SELECT Students.*
FROM Students LEFT JOIN
(SELECT StudentID
FROM StudSport
WHERE SportID = 3) as Footballers
ON Students.StudentID = Footballers.StudentID
WHERE Footballers.StudentID is Null
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
.