View Course
?id=149

Course
    Events (Live or Virtual)
        Registrations
----------------------------------------------------------------------------------------------------
view sample

Online:
Price: $500.00 usd
[ Register ]   
------------------------------
Virtual:
There is a minimun of 6 students required
per virtual event.
[ 9/30/2020 - 9/30/2020 $750 usd ]
[ 10/30/2020 - 10/30/2020 $750 usd ]
[ 11/30/2020 - 11/30/2020 $750 usd ]
------------------------------
Live:
There is a minimun of 10 students required
per virtual event.
[ Phoenix, AZ 8/15/2020 - 8/18/2020 $750 ]
[ Los Angeles, CA 10/15/2020 - 10/18/2020 $950 ]
------------------------------


SELECT course details

    COURSE
    course_ID	course_Title
    ---------   ------------
    149	        Virtual Online Training Class


SELECT class event details
    RELATE COURSE.course_ID = COURSE_SCHED.course_sched_Course

    COURSE_SCHED
    course_sched_ID course_sched_Course
    --------------- -------------------
    349	            149
    350	            149


SELECT Registration details

    RELATE COURSE_SCHED.course_sched_ID TO REG_REGISTRATION.reg_sched_ID

    REG_REGISTRATION
    reg_ID	reg_sched_ID
    ------  ------------
    13680	349
    13679	349


SELECT student details
    RELATE REG_REGISTRATION.reg_ID TO REG_STUDENT.student_reg_ID

    REG_STUDENT
    student_ID	student_reg_ID	student_EMail
    ----------  --------------  -------------
    11829	    13679		    jeanine.chavez@no-mail.com
    11830	    13679	        molly.mcgee@no-mail.com
    11832	    13679	        molly.mcgee@no-mail.com
    11833	    13680	        jay.eaton@no-mail.com
    
    
    

course_ID
course_course_catg_ID
course_Title
course_Desc
...
rsEvents
    course_sched_ID
    course_sched_Course (FK course_ID)
    course_sched_Location
    course_sched_StartDate
    course_sched_EndDate
    course_sched_StartDisplay
    course_sched_EndDisplay
    course_sched_Enabled
    course_sched_Price
    course_sched_Info
    course_sched_Import_ID
    course_sched_Type

----------------------------------------------------------------------------------------------------
SHAPE{ SELECT course.* FROM course WHERE (((course.course_ID)=149));}
APPEND({
    SELECT course_sched.* FROM course_sched
} AS rsEvents
RELATE course_ID TO course_sched_Course)
----------------------------------------------------------------------------------------------------

SHAPE{ SELECT course_sched.*  FROM course_sched WHERE (((course_sched.course_sched_Course)=149));}
APPEND({
    SELECT reg_Registration.*, reg_Student.student_EMail
    FROM reg_Registration LEFT JOIN reg_Student ON reg_Registration.reg_ID = reg_Student.student_reg_ID
} AS rsRegs
RELATE course_sched_ID TO reg_sched_ID)
----------------------------------------------------------------------------------------------------

SHAPE {SELECT course.* FROM course WHERE (((course.course_ID)=149));}
APPEND (
    (
        SHAPE {SELECT course_sched.* FROM course_sched}
        APPEND (
            {
                SELECT reg_Registration.*, reg_Student.student_EMail
                FROM reg_Registration LEFT JOIN reg_Student ON reg_Registration.reg_ID = reg_Student.student_reg_ID            
            }
            AS rsReg
            RELATE course_sched_ID TO reg_sched_ID
        )
    ) AS rsEvents
    RELATE course_ID TO course_sched_Course
)

====================================================================================================

SHAPE{PARENT_QUERY}
APPEND({
    CHILD_QUERY
} AS rsChild
RELATE parent_ID TO child_ID)


----------------------------------------------------------------------------------------------------
SHAPE {PARENT_QUERY}
APPEND (
    (
        SHAPE {CHILD_QUERY}
        APPEND (
            {GRANDCHILD_QUERY}
            AS rsGrandchild
            RELATE child_ID TO grandchild_ID
        )
    ) AS rsChild
    RELATE parent_ID TO child_ID
)





SHAPE {SELECT course.* FROM course WHERE (((course.course_ID)=149));}
APPEND (
    (
        SHAPE {SELECT course_sched.* FROM course_sched WHERE (((course_sched.course_sched_Type)="Virtual"));}
        APPEND (
            {
                SELECT reg_Registration.*, reg_Student.student_EMail
                FROM reg_Registration LEFT JOIN reg_Student ON reg_Registration.reg_ID = reg_Student.student_reg_ID            
            }
            AS rsReg
            RELATE course_sched_ID TO reg_sched_ID
        )
    ) AS rsVirtualEvents
    RELATE course_ID TO course_sched_Course
)


SELECT course_sched.*, course.*, 
IIf(course_sched.course_sched_Type='Live', 
    IIf(course_sched.course_sched_Price>0,course_sched.course_sched_Price,course.course_price_Live), 
    IIf(course_sched.course_sched_Price>0,course_sched.course_sched_Price,course.course_price_Virtual) 
    ) AS course_Price,
(SELECT COUNT(*) as cnt 
FROM reg_Registration INNER JOIN reg_Student ON reg_Registration.reg_ID = reg_Student.student_reg_ID 
WHERE (((reg_Registration.reg_sched_ID)=course_sched.course_sched_ID)); 
) AS StuCnt
FROM course LEFT JOIN course_sched ON course.course_ID = course_sched.course_sched_Course 
WHERE (((course_sched.course_sched_ID)=349));




SELECT course_sched.*, 
(SELECT COUNT(*) as StuCnt 
FROM reg_Registration INNER JOIN reg_Student ON reg_Registration.reg_ID = reg_Student.student_reg_ID 
WHERE (((reg_Registration.reg_sched_ID)=course_sched.course_sched_ID)); 
) AS cnt, course.min_heads_Virtual 
FROM course LEFT JOIN course_sched ON course.course_ID = course_sched.course_sched_Course 
WHERE (((course_sched.course_sched_Course)=149) 
AND ((course_sched.course_sched_Enabled)=True) 
AND ((course_sched.course_sched_Type)='Virtual') 
AND ((course_sched.course_sched_StartDisplay)<=Now()) 
AND ((course_sched.course_sched_EndDisplay)>=Now())) 
ORDER BY course_sched.course_sched_StartDate;


SELECT course_sched.*, course.*, 
IIf(course_sched.course_sched_Type='Live', 
  IIf(course_sched.course_sched_Price>0,course_sched.course_sched_Price,course.course_price_Live), 
  IIf(course_sched.course_sched_Price>0,course_sched.course_sched_Price,course.course_price_Virtual) 
) AS course_Price 
FROM course LEFT JOIN course_sched ON course.course_ID = course_sched.course_sched_Course 
WHERE (((course_sched.course_sched_ID)=350));