problem statement

I now this is not fair as you do not have the Entity Relationship Diagram for the database, but it's something to think about.
using SQL, answer the following question.
which camp events (like archery) is a camper allowed to join?
conditions: they can only join events that are not full.
they can remain in a camp that is full during an edit if they do not choose an alternate class.
only return classes the camper may join (or stay in)

solution:
IF @Type = 'U' --we're a user asking the question, not an administrator
Begin
SELECT Event.Event_ID, Event.Event_Name,
(Select Count(@Camper_ID) from Camper_Event where Camper_Event.Event_ID = Event.Event_ID and Camper_Event.Camper_Camp_Event_ID=@Camp_Event_ID And Camper_Event.Camper_ID=@Camper_ID And Camper_Event.Camper_Event_Period_Number=@PeriodID) as camperatclass,
(Select Event.Event_Capacity) as Capacity,
(Select Count(@Camper_ID) from Camper_Event where Camper_Event.Event_ID = Event.Event_ID and Camper_Event.Camper_Camp_Event_ID=@Camp_Event_ID And Camper_Event.Camper_Event_Period_Number=@PeriodID) as atclass
FROM Class_Period INNER JOIN
Period_Event ON Class_Period.Class_Period_ID = Period_Event.Class_Period_ID INNER JOIN
Camp_Session_Event ON Period_Event.Camp_Session_Event_ID = Camp_Session_Event.Camp_Session_Event_ID INNER JOIN
Event ON Camp_Session_Event.Event_ID = Event.Event_ID
WHERE (Camp_Session_Event.Camp_Session_ID = @campsessionid) AND
(Class_Period.Period_Number = @PeriodID) and
(((Select Event.Event_Capacity) > (Select Count(@Camper_ID) from Camper_Event where Camper_Event.Event_ID = Event.Event_ID and Camper_Event.Camper_Camp_Event_ID=@Camp_Event_ID And Camper_Event.Camper_Event_Period_Number=@PeriodID)) OR --capacity!
(Select Count(@Camper_ID) from Camper_Event where Camper_Event.Event_ID = Event.Event_ID and Camper_Event.Camper_Camp_Event_ID=@Camp_Event_ID And Camper_Event.Camper_ID=@Camper_ID And Camper_Event.Camper_Event_Period_Number=@PeriodID)>0) --in class
And -1 < (Select Count(@Camper_ID) from Camper_Event where Camper_Event.Event_ID = Event.Event_ID and Camper_Event.Camper_Camp_Event_ID=@Camp_Event_ID And Camper_Event.Camper_ID=@Camper_ID)
ORDER BY Event.Event_Name
end


conclusion: that is one HAIRY statemtn to write by hand. bummer i dont' know how to do it in a GUI tool.

this kinda makes my modification to the blogger hack look a little less impressive!

Edit: ho ho ho. vs7 reversed it and drew it in the gui for me. nice.