Last post Jan 22, 2015 01:48 PM by Apierre
Jan 22, 2015 09:34 AM|Apierre|LINK
I am programming a class schedule and I have a page where an administrator assigns a substitute teacher to a class. On the page I have 2 drop downs that are populated with the coach names from a table called CLASSES. The user picks the current coach from
the first drop down and also the day and time of the class that needs a sub. I want to populate the second drop down with coaches that do not have a class at that time.
My current query:
SELECT DISTINCT [COACHNAME] FROM [CLASSES] WHERE [CLASSDAY] <> 'Monday' AND [STARTTIME] <> '8:00 PM' ORDER BY [COACHNAME]
The problem with this query is that the same COACHNAME can be in the CLASSES table multiple times therefore the query still retrieves the COACHNAME for coaches who have a class at that time because their name appears in records that pass the conditions.
Jan 22, 2015 10:19 AM|JoyceW|LINK
You will need the starttime and the endtime of the class to make this work.
SELECT DISTINCT [COACHNAME] FROM [CLASSES] WHERE [CLASSDAY] <> 'Monday' AND [STARTTIME] not between '8:00 PM' and '10:00 PM' order by [COACHNAME]
Jan 22, 2015 12:37 PM|gimimex|LINK
Try something like this:
FROM [CLASSES] as c
(select 1 from [CLASSES] as s
s.[COACHNAME] = c.[COACHNAME] AND
s.[CLASSDAY] = 'Monday' AND
s.[STARTTIME] = '8:00 PM')
Hope this helps.
Jan 22, 2015 01:48 PM|Apierre|LINK
Thanks! It worked just fine