Query help please - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Query help please

Author

Topic

donkyho
Starting Member

12 Posts

Posted - 2011-10-21 : 18:26:07

I have 4 tables

tblCourses (this is simply a lookup table, ID and course name)
tblCourseSchedules (Course reference, start date and end date are main fields here)
tblCourseScheduleTimes (day of week, start time, end time)
tblClassScheduleCancellations (time reference and cancellation date)

each table relates to the next, in order as I listed them, with the previous table's primary key. So tblCourseSchedules has a reference to tblCourses, etc.

tblClassScheduleCancellations has data about classes that have been cancelled on same day. What I need is this.

1 row for each course that has 1 or more cancellations. the following data returned tblCourses.class_name, tblCourseSchedules.id, tblClassScheduleCancellations.cancellation_date

I set up data with the following

Course Cancel Date StTime EndTime
Yoga 2011-10-21 00:00:00.000 1:00 AM 1:00 AM
Yoga 2011-10-21 00:00:00.000 5:00 PM 6:00 PM
Yoga 2011-10-22 00:00:00.000 1:00 AM 1:00 AM
Yoga 2011-10-22 00:00:00.000 11:00 AM 12:30 PM

I don't need the times returned, only the Course Name, Cancel Date and Course Schedule ID...when I run my script I get all 4 rows returned still. Here's what I have

SELECT Types.class_name, Schedules.id, Cancellations.cancellation_date
FROM tblCourses Types
INNER JOIN tblCourseSchedules Schedules ON Schedules.class_id = Types.id
INNER JOIN tblCourseScheduleTimes Times ON Times.class_schedule_id = Schedules.id
INNER JOIN tblClassScheduleCancellations Cancellations ON Cancellations.class_schedule_time_id = Times.id

Would love some help with this one if possible.
Thanks

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-10-21 : 19:09:08

Which cancellation date(s) should be returned? IOW, what is the expected output? If it is just one then use MAX(cancellation_date) or MIN(cancellation_date) and GROUP BY the other columns.

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

donkyho
Starting Member

12 Posts

Posted - 2011-10-21 : 19:24:10

yeah, I just realized that each course has 2 different cancellation dates, hence my problem. Guess I can't do this unless I forget about posting the cancellation date.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-10-21 : 19:54:02

Well, you can if you can define which date is "correct". If any will do then the MIN or MAX approach will suffice. If both days (21st and 22nd) should appear then use DISTINCT, CAST/CONVERT the dates to DATE and GROUP BY the remaining columns returned.

=======================================
Faced with the choice between changing one's mind and proving that there is no need to do so, almost everyone gets busy on the proof. -John Kenneth Galbraith

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources