-- Select all those events (and their event times) that have no task data 
-- and belong to the schedule that is assigned to the same usergroup as the user.
-- Ordered by date and starting time.

-- Old version. No schedule or group info.
-- SELECT * FROM EVENT 
-- LEFT OUTER JOIN EVENTTIME ON EVENTTIME_eventTimeID = EVENT_EVENTTIME_eventTimeID 
--     WHERE EVENTTIME_eventTimeID > -1 
--           AND EVENT_eventID NOT IN 
--          (SELECT DISTINCT EVENT_eventID 
--           FROM EVENT LEFT OUTER JOIN TASK ON TASK_EVENT_eventID = EVENT_eventID 
--           LEFT OUTER JOIN CHOICE ON CHOICE_TASK_contains_taskID = TASK_taskID 
--           LEFT OUTER JOIN TASKDATA ON TASKDATA_CHOICE_choiceID = CHOICE_choiceID 
--              WHERE TASKDATA_USER_userID  = [---USER ID GOES HERE!!!---] AND EVENT_rowStatus = 'ACTIVE') 
--           AND EVENT_SCHEDULE_scheduleID IN 
--               (SELECT SCHEDULE_scheduleID FROM SCHEDULE
--                LEFT OUTER JOIN 
--                     (SELECT BELONGS_USERGROUP_usergroupID AS 'userGroupID' FROM BELONGS WHERE BELONGS_USER_userID = [---USER ID GOES HERE!!!---] )  BELONGS_UG
--                ON SCHEDULE_USERGROUP_usergroupID = userGroupID WHERE SCHEDULE_USERGROUP_usergroupID = userGroupID )
-- ORDER BY EVENTTIME_year, EVENTTIME_month, EVENTTIME_day, EVENTTIME_fromHour, EVENTTIME_minute ASC

-- New version, with schedule and group info.
SELECT * FROM EVENT 
LEFT OUTER JOIN 
    (SELECT  SCHEDULE_scheduleID, SCHEDULE_description,  USERGROUP_usergroupID, USERGROUP_description  
    FROM SCHEDULE
    LEFT OUTER JOIN (
        SELECT USERGROUP_usergroupID, USERGROUP_description FROM BELONGS 
        LEFT OUTER JOIN USERGROUP ON BELONGS_USERGROUP_usergroupID = USERGROUP_usergroupID
        WHERE BELONGS_USER_userID = [---USER ID GOES HERE!!!---] ) as Usergroup 
        ON SCHEDULE_USERGROUP_usergroupID = USERGROUP_usergroupID) 
    as eventSchedule ON SCHEDULE_scheduleID = EVENT_SCHEDULE_scheduleID
LEFT OUTER JOIN EVENTTIME ON EVENTTIME_eventTimeID = EVENT_EVENTTIME_eventTimeID 
    WHERE EVENTTIME_eventTimeID > -1 
          AND EVENT_eventID NOT IN 
         (SELECT DISTINCT EVENT_eventID 
          FROM EVENT LEFT OUTER JOIN TASK ON TASK_EVENT_eventID = EVENT_eventID 
          LEFT OUTER JOIN CHOICE ON CHOICE_TASK_contains_taskID = TASK_taskID 
          LEFT OUTER JOIN TASKDATA ON TASKDATA_CHOICE_choiceID = CHOICE_choiceID 
             WHERE TASKDATA_USER_userID  = [---USER ID GOES HERE!!!---] AND EVENT_rowStatus = 'ACTIVE') 
          AND EVENT_SCHEDULE_scheduleID IN 
              (SELECT SCHEDULE_scheduleID FROM SCHEDULE
               LEFT OUTER JOIN 
                    (SELECT BELONGS_USERGROUP_usergroupID AS 'userGroupID' FROM BELONGS WHERE BELONGS_USER_userID = [---USER ID GOES HERE!!!---] )  BELONGS_UG
               ON SCHEDULE_USERGROUP_usergroupID = userGroupID WHERE SCHEDULE_USERGROUP_usergroupID = userGroupID )
ORDER BY EVENTTIME_year, EVENTTIME_month, EVENTTIME_day, EVENTTIME_fromHour, EVENTTIME_minute ASC
