Timetable Database - Show Member Timetable Clashes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Timetable Database - Show Member Timetable Clashes

Author

Topic

jd307
Starting Member

2 Posts

Posted - 2008-05-14 : 08:41:22

Hi all! I am doing my final piece of coursework for my degree course for this year and I am stuck on this SQL query. Basically, the requirements are to write a query to show members who have two or more sessions occuring at the same time. For example, Dave might have been timetabled for a guitar lesson at 9am AND a piano lesson at the same time (because the person updating the information wasn't doing their job properly or something). The query needs to pull out the fields where these times are duplicating for individual members and not display the information where it isn't clashing.

My tables are as follows:

member (memberID, firstName, lastName)
session  (sessionID, roomName, title, startTime)
member_session  (memberID, sessionID)

I have created this SQL statement so far which shows all of the sessions each member is enrolled on:

SELECT RTRIM(lastName)+', '+RTRIM(firstName) AS Full_Name, title, roomName, startTime
FROM session, member, member_session
WHERE member_session.memberID = member.memberID
AND member_session.memberID = member_session.memberID
AND member_session.sessionID = session.sessionID
AND startTime = startTime
ORDER BY lastName, firstName;

This therefore shows information such as:


Full_Name       title      roomName   startTime
-----------------------------------------------
Dillan, Bob     piano      1          09:00:00
Dillan, Bob     guitar     2          09:00:00
Dillan, Bob     orchestra  4          12:00:00
Cooper, Alice   piano      1          09:00:00
Cooper, Alice   choir      3          12:00:00
Dunn, Jeff      guitar     2          09:00:00
Dunn, Jeff      flute      1          09:00:00

As you can see from this, both Bob and Jeff have two lessons at 9am. This cannot happen, so the query should show those clashing records, e.g:


Full_Name       title      roomName   startTime
-----------------------------------------------
Dillan, Bob     piano      1          09:00:00
Dillan, Bob     guitar     2          09:00:00
Dunn, Jeff      guitar     2          09:00:00
Dunn, Jeff      flute      1          09:00:00

As Alice has no clashes, her sessions shouldn't be seen. Nor should Bob's lesson at 12 as that is not clashing with his own timetable either.
The only problem with this example is it shows all clashes at 9am. This might not be the case so I need to check if startTime is the same when memberName (or memberID) is the same. If that makes sense.

I have been racking my brains out on this for about 2 days now and cannot think of how to do it. Any help would be appreciated!

Thanks!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-05-15 : 09:06:03

Hint: Investigate the GROUP BY and HAVING clauses.

jd307
Starting Member

2 Posts

Posted - 2008-05-15 : 09:24:58

Hey Andrew. Thanks for your tip. I have now solved this issue.

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 works perfectly now. Thank you!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2008-05-15 : 11:24:49

"Teach a man to fish", etc, etc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources