SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Jun 2006
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Creating a course Schedule table - Help Needed on Correct query

    Hi this is my first time in this forum so here goes....

    im creating a quick form that retrieves a list of schedules based on the course chosen....in fact the form i am looking to emulate is this form
    http://www.interquad.com/search/scheduleSelector.asp


    now within my Mysql database i have the following simple tables

    Course: CourseID,CourseName,TeacherID,coursefee,max

    Enrollment:StudentID,CourseID

    Students:StudentID,Firstname,Surname,BirthDate

    Teachers:TeacherID,FirstName,Surname


    my approach was to create a new table called course_schedule so that i can use it to create a start_date,location,num_enrolled.


    can anyone please help me as to how i can approach this in order to achieve the form shown on http://www.interquad.com/search/scheduleSelector.asp

    thank you

    From a Desparate Newbie!

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,578
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Drop the num_enrolled column, and change your Enrollment table to reference the new Schedule table instead of the Course table. That way you know who and how many are enrolled in a specific offering of a course, and can still get to the course info through the middle table. If more than one teacher can teach the same course (one teaches it in fall, one teaches it in spring?) then the teacherid belongs on the Schedule table instead of the Course table. Otherwise it looks good.

  3. #3
    SitePoint Member
    Join Date
    Jun 2006
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question

    so if i wanted to retrieve the number of times a course was scheduled in any particular month.

    would i have to use the join mechanism.

    lest assume i've decided to go for:


    course_schedule:course_id,start_date,location,teacher_id


    how can i join the details from course_schedule with course and show a readable list displaying the coursename,course_id,start_date,location,teachersname,fee.

    this query would have to involve the following 3 tables courses,course_schedule and teachers.

    please enlighten me

  4. #4
    SitePoint Member
    Join Date
    Jun 2006
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up

    well after a bit of researching i made this query and ti works perfect



    SELECT course_schedule.course_id,course.CourseName,course_schedule.start_date,course.fee
    FROM course_schedule
    LEFT JOIN course
    ON course_schedule.course_id =course.CourseID
    LEFT JOIN teachers
    ON course_schedule.teacher_id =teachers.TeacherID
    WHERE(course_schedule.course_id='1')

    thank you very much for your help

  5. #5
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    why are you using LEFT joins? and why are you joining the teachers table if you are using any columns from that table?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •