[SOLVED] Timetabling System - Enrollment on Clashing Sessions

[jd307]

Good evening my fellow SQL lovers!

 

Strictly speaking, this isn't a MySQL issue as I am creating this in MS Access 2003 (a requirement for the system).

 

Now here is the objective:

 

The system is to be able to hold a timetable for a Saturday School.  Members join and the person in charge timetables them in (depending on space, etc) to their chosen subjects.  Now some subjects are quite popular, so on the application form have to put a 1st and 2nd choice.  Each member can ONLY do one of these choices as they both start at 9am therefore they will have two sessions going on at once.  Obviously, this can't happen.

The database is built and fine, the only problem I am having now is the system needs a kind of 'checker'.  This is to basically, when the SQL query is run, to show a list of all the members who have two (or more) sessions with the same start time.

 

The problem is, I am not that good at SQL and even though I have a handly book on it, I cannot figure this one out at the moment.

 

What I have tried:

 

SELECT member_session.memberID, session.sessionID, startTime
FROM session, member_session
WHERE member_session.sessionID = session.sessionID
AND startTime = startTime

 

I have also tried various other combinations with memberID, etc. but these are all guesses and haven't worked.  The output should be something like:

 

memberID    sessionID    startTime

-----------------------------------

    1                2          09:00:00

    1                3          09:00:00

    6                1          09:00:00

    6                8          09:00:00

 

This is basically so that the coordinator can run this query (currently will be run as a query inside Access 2003) and it will show ONLY the members with the duplicated times and ONLY the duplicated times (for example, member with ID 1 will also have sessions at 10:00:00 and 11:00:00, etc. but we dont want that to show).

 

The tables that are needed for this (or may be) are as follows:

 

member                |  memberID  |  firstName  |  lastName  |

session                | sessionID  |  startTime  |  title  |

member_session    | sessionID  | memberID |

 

Any more info that you require please let me know and I will provide it.  The tables cannot be changed as this is the format they must be in.

 

Any assistance (even if just code pointers that I should look up to be used for this) would be VERY appreciated!

 

Thanks

[jd307]

Hmm, I have had more of a thought now that I am awake properly and have taken a better approach to this.  The SQL statement that I have at the moment is as follows:

 

SELECT firstName, lastName, title, roomName, startTime
FROM session, member, member_session
WHERE member_session.memberID = member.memberID
AND member_session.sessionID = session.sessionID
AND startTime = startTime
ORDER BY lastName, firstName;

 

This gives an output that shows every session that every member is enrolled on.  This is perfect, except I need to exclude all rows for members that do not have the same startTime (if this makes sense).

 

E.g.

firstName  lastName  title          roomName    startTime
------------------------------------------ --------------
Dave        Mann       Flute        Foundation   09:00:00
Dave        Mann       Piano        Hall             09:00:00
Dave        Mann       Music Club 2               12:00:00
Alice        Cooper     Choir        3                09:00:00
Alice        Cooper     Clarinet     Library        12:00:00
Bob          Dillan       Guitar       2               09:00:00
Bob          Dillan       Adv. Guitar 4              09:00:00
Bob          Dillan       Clarinet     Library        12:00:00

 

As you can see this lists everything.  What I want is to show ONLY (for example) where Dave has two sessions that are starting at 09:00:00.  He cannot be in two places at once, so it needs to display these two as they are the same start time for Dave, but then show the same again for Bob as he has two at 9am as well.  Alice has no clashing sessions, so she doesn't need to be displayed at all, nor does Dave's or Bob's 12pm sessions as they are not clashing with anything else they are already signed up to.  So the output should be:

 

firstName  lastName  title          roomName    startTime
------------------------------------------ --------------
Dave        Mann       Flute        Foundation   09:00:00
Dave        Mann       Piano        Hall             09:00:00
Bob          Dillan       Guitar       2               09:00:00
Bob          Dillan       Adv. Guitar 4              09:00:00

 

I think this makes more sense than before... I hope.  Though bear in mind not all clashes may appear at 9am so I cannot use a set number in the criteria.

 

Thanks all

[jd307]

After a little tinkering with ideas, etc. I have found the solution to my problem.

 

SELECT 	member.memberID, member.firstName, member.lastName, 
COUNT(session.sessionID) AS NumberEnrolledOn,
	session.startTime
FROM 	session, member, member_session
WHERE 	member.memberID = member_session.memberID
AND 	member_session.sessionID = session.sessionID
GROUP BY member.memberID, session.startTime, member.firstName, 
member.lastName
HAVING COUNT(session.sessionID) > 1;

 

This now gives the correct output!

[mezise]

If you want to have the output you first posted (with all clashing sessions not grouped by member):

firstName  lastName  title          roomName    startTime
------------------------------------------ --------------
Dave        Mann       Flute        Foundation   09:00:00
Dave        Mann       Piano        Hall             09:00:00
Bob          Dillan       Guitar       2               09:00:00
Bob          Dillan       Adv. Guitar 4              09:00:00

 

you need to INNER JOIN second time with session table but only with records you want to display and next GROUP by memberID and sessionID.