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
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
