I have this query:
CREATE SYNONYM TransServe
FOR .......
CREATE SYNONYM TransServeRoutes
FOR .......
SELECT Bus_Routes.Schedule_ID, Trans.RecordID, Trans.Prefix + ' ' +
Trans.Name + ' ' + Trans.Type + ' ' + Trans.Suffix + '@' + ' ' + Trans1.Name
+ ' ' + Trans1.Type + ' ' + Trans1.Suffix AS StreetName,
Bus_Routes.Route_ID, TransRoutes.VisibleRouteID AS Route_Name,
Schools.School_name,
Bus_Routes.Side, Bus_Routes.Pick_Up,
Bus_Routes.Drop_Off
FROM Bus_Routes INNER JOIN TransServe Trans ON
Bus_Routes.Main_St_ID = Trans.RecordID
INNER JOIN TransServe Trans1 ON
Bus_Routes.X_Street_ID = Trans1.RecordID
INNER JOIN TransServeRoutes TransRoutes ON
Bus_Routes.Route_ID = TransRoutes.RecordID
INNER JOIN Schools ON Bus_Routes.School_ID =
Schools.School_ID
WHERE (Bus_Routes.School_ID = 10)
AND ModifiedSchedule = 0
DROP SYNONYM TransServe
DROP SYNONYM TransServeRoutes
I have another table called ModifiedSchedule which has one field,
School_ID. The idea is that if we have inclement weather here in our
school district, the transportation department can activate modified
schedules. When they do, I query the BusRoutes table for schedules with
the modified flag and insert it into the ModifiedSchedules table, so
when a parent clicks on the bus schedule for a route that is modified,
they see that. This works great for the elementary school up in the
mountains where all of their routes are modified. However, for the high
schools here, the routes for the mountain kids changes but the route for
the kids living in the desert do not get changed. So what I need to do
is to output all the normal schedules, replacing the modified routes
with the modified information. I know that I can do this in SQL, and I
would rather the stored procedure handle this logic rather than the
ColdFusion side, but I don't know how to change the statement above. I
know that I need to get rid of the AND operator here and change it
somehow, but again I am not sure how to go about this.
Oh, and in the BusSchedules table, there is a field called
ModifiedSchedule, which is a bit field. Either 1 or 0.
Thanks,
Bruce
*
*
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to
date
Get the Free Trial
http://ad.doubleclick.net/clk;160198600;22374440;w
Archive: http://www.houseoffusion.com/groups/SQL/message.cfm/messageid:3014
Subscription: http://www.houseoffusion.com/groups/SQL/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.6
