What are the columns that are somewhat linked between these tables?
----- Original Message -----
From: "Todd Ashworth" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, January 03, 2001 3:32 PM
Subject: OT: SQL Help
> Sorry about posting this to the wrong list, but I'm having trouble getting
> mail through to the SQL list.
>
> I am running a query to see if there is an entry in the database for an
> event that matches certain date criteria. One of the criteria is that one
> of the dates that the event is scheduled on does not fall within a certain
> list of dates that are stored in the "exception_dates" table.
>
> All of the query works except for: AND NOT EXISTS (SELECT 1
> FROM
> exception_dates ed
> WHERE
> ed.exception_dates = '#year#-#month#-#week_day# 00:00:00'))
>
> The way it should work is only return a record if there is no instance of
> the current year-month-day matching anything in the exception_dates table.
>
> Here is the whole query:
>
> SELECT 1 FROM dummy WHERE EXISTS
> (SELECT 1 FROM teacher_schedule ts
> WHERE ts.contact_id = #Request.TempContactID#
> AND '#year#-#month#-#week_day#' BETWEEN ts.start_date AND
> ts.end_date
> AND (ts.start_time_1 <> ts.end_time_1 OR ts.start_time_2 <>
> ts.end_time_2)
> AND (SELECT days
> FROM days_times dt
> WHERE dt.days_times_id = ts.start_time_1) =
> '#DayOfWeekAsString(week_day)#'
> AND NOT EXISTS (SELECT 1
> FROM
> exception_dates ed
> WHERE
> ed.exception_dates = '#year#-#month#-#week_day# 00:00:00'))
>
> What am I missing?
>
> Todd Ashworth
>
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
