how to compare two tables fields name with another value in mysql?
- by I Like PHP
I have two tables
table_school
school_open_time|school_close_time|school_day
8:00 AM | 9:00PM | Monday
10:00 AM | 7:00PM | Wednesday
table_college
college_open_time|college_close_time|college_day
10:00 AM | 8:00PM | Monday
10:00 AM | 9:00PM | Tuesday
10:00 AM | 5:00PM | Wednesday
Now I want to select school_open_time school_close time, college_open_time and college_close_time according to today (means college_day=school_day=today), and also if there is no row for a specific day in any of one table then it display blank field ( LEFT JOIN , I think I can use).
Please suggest me best and optimized query for this.
UPDATE:
if there is no open time and close time for school then college_open_time and college_close_time has to be returned( not to be filled in database,just return) as school_open_time and school_close_time. and there always must be college_open_time and college_close_time for a given day
MORE UPDATE:
i m using below query
SELECT college_open_time,college_close_time ,school_open_time,
school_close_time FROM tbl_college
LEFT JOIN tbl_school ON school_owner_id=college_owner_id
WHERE college_owner_id='".$_session['user_id']."' AND
college_day='".date('l',time())."'";
it return single row (left hand having some value and right hand having blank value) when there is no row of a given day in table_school, BUT display seven rows with same value on left hand side(college_open_time, college_close_time) and 6 blank row on right hand side (school_open_time and school_close_time)
i need only one row when both table have a row of a given day
but using above query take only first row of corresponding table_school where school_owner_id is 50(let), it not see the condition that school_day name should be given day
