SQL Query Problem

[ColinTod]

Hi all,
I am having a problem with a report I am trying to produce.
I am quite new at SQL so I am sure that the answer is basic.

I have a DB I have designed for my local Martial arts club to handle student attendances.
3 of the tables are:

TABLE PERSON
PERSON_ID FIRST_NAME LAST_NAME Otherfields…
1 Colin Tod
2 Heather Tod
3 John Smith
4 Paul Polson
5 Fred Potter
6 Ann Dons
7 Matt Roy

TABLE TRAINING_HISTORY
PERSON_ID SESSION_ID Other Fields…
1 1
2 1
3 1
4 1
5 1
6 1
7 1
1 2
2 2
3 2
4 2
5 2
6 2
7 2
1 3
2 3
3 3
4 3
5 3
6 3

TABLE SESSION
SESSION_ID SESSIONDATE Other Fields …
1 2/05/2007
2 4/05/2007
3 7/05/2007
4 9/05/2007
5 11/05/2007
6 14/05/2007
7 16/05/2007
8 18/05/2007
9 21/05/2007
10 23/05/2007
11 25/05/2007


I have a script that gives me a list of students who have trained
between 2 dates and the number of trainings they have done.
===========================================
SELECT DISTINCT person.first_name, person.last_name, count(*) as
TrainingCount
FROM Session INNER JOIN
(Person INNER JOIN Training_History ON Person.Person_ID =
Training_History.Person_ID) ON
Session.Session_ID = Training_History.Session_ID
WHERE SESSION.SESSIONDATE between Date1 and Date2
and (ACTIVATED = 'Y')
GROUP BY person.first_name, person.last_name
order by LAST_NAME
===========================================
(Date1 and Date2 are veriables)

What I am looking for is a script that gives a list of students who
have NOT trained in this period.
I have tried:
SESSION.SESSIONDATE not between Date1 and Date2
This of course produces a list of everyone who has trained outside the
2 dates. This is not the same thing.
If Colin trained both outside and between the dates and Heather and John trained outside but not between the dates then I only want Heather and John to be returned.

I am told that I need to play with Outer Joins but a still can't produce what I am after.

Can anyone help????


Colin

 

[r937]

change your first query slightly --

SELECT person.first_name
     , person.last_name
     , count(*) as TrainingCount
  FROM Session 
INNER 
  JOIN Training_History
    ON Training_History.Session_ID = Session.Session_ID
INNER
  JOIN Person
    ON Person.Person_ID = Training_History.Person_ID
 WHERE Session.SessionDate between Date1 and Date2
   AND Session.Activated = 'Y'
GROUP 
    BY person.last_name
     , person.first_name
ORDER 
    BY person.last_name
     , person.first_name

was i right in assuming the ACTIVATED column is in the Session table?

note that DISTINCT is not required when the SELECT contains only the GROUP BY columns, because the groups in a GROUP BY query are distinct by definition

r937.com | rudy.ca

 

[ColinTod]

Thanks for that. I can see that it is a more elegant way to write the original Query and I will change my other Queries to that format. Can anyone answer the original question though?
Colin

 

[r937]

well if my rewrite worked, then this should give you what you want --

SELECT person.first_name
     , person.last_name
  FROM Session 
CROSS
  JOIN Person
LEFT OUTER 
  JOIN Training_History
    ON Training_History.Session_ID = Session.Session_ID
   AND Training_History.Person_ID = Person.Person_ID 
 WHERE Session.SessionDate between Date1 and Date2
   AND Session.Activated = 'Y'
   AND Training_History.Session_ID IS NULL
GROUP 
    BY person.last_name
     , person.first_name

r937.com | rudy.ca

 

[ColinTod]

Thanks. Small rewrite required but that did it.