#1
  1. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,989
    Rep Power
    375

    Outer join two tables - need help


    ok I have few tables:

    classes (class_id, teacher_id)
    class_timing (id. class_id, day, time) ## id = time_id
    schedule (schedule_id, time_id, date)

    What i am hoping to get is a table like:

    Class Title | Time | Link_to schedule
    CA | 12:00 | link
    BC | 11:00 | no schedule

    This is the query i am using:

    Code:
    SELECT c.class_title, ct.time, s.schedule_id
    FROM classes c
    INNER JOIN class_timings ct
    ON ct.class_id = c.class_id
    LEFT OUTER JOIN schedule s
    ON s.time_id = ct.id
    WHERE c.teacher_id = 1
    AND ct.day = DAYNAME(CURDATE())
    AND s.class_date  = curdate()
    The problem is:

    AND s.class_date = curdate().

    so i want to get today's schedule classes. IF i ommit this then query returns all schedule. If i add that in then it doesnt return any rows when in fact what i want to happen is to return the class_title and timing (i.e. there is no corresponding row in schedule table) so i can prompt user to schedule it in..

    I can do this in two queries or using application but I thought it would be helpful if a query can help
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    change this --
    Code:
    LEFT OUTER JOIN schedule s
    ON s.time_id = ct.id
    WHERE c.teacher_id = 1
    AND ct.day = DAYNAME(CURDATE())
    AND s.class_date  = curdate()
    to this --
    Code:
    LEFT OUTER JOIN schedule s
    ON s.time_id = ct.id
    AND s.class_date  = curdate()
    WHERE c.teacher_id = 1
    AND ct.day = DAYNAME(CURDATE())
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Dec 2004
    Posts
    2,989
    Rep Power
    375
    thanks (once again) didn't think/know I could do that. I was thinking some kind of subquery/nline view might be needed..

IMN logo majestic logo threadwatch logo seochat tools logo