how to compare two tables fields name with another value in mysql?

Question

I have two tables

1. table_school

   school_open_time|school_close_time|school_day
   8:00 AM         | 9:00PM          | Monday
   10:00 AM        | 7:00PM          | Wednesday
   

2. 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

---

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

---

More UPDATE @37Stars

There is a little bit problem also Dear, datatype of school_close_time and school_open time is TIME type whereas datatype of college_open_time and college_close_time is VARCHAR type. i used below code given by you but i modified a bit and i m getting close to result,

but now tell me where i have to write IFNULL in below code segment

*IFNULL(TIME_FORMAT()) Or TIME_FORMAT(IFNULL())*

SELECT TC.owner_id,college_open_time AS collegeOpen, 
       college_close_time AS collegeClose, 
TIME_FORMAT(school_open_time, '%h:%i %p' ) AS schoolOpen,       
TIME_FORMAT(school_close_time, '%h:%i %p' ) AS schoolClose
FROM tbl_college TC
LEFT JOIN tbl_school  TS ON TS.owner_id = TC.owner_id
AND TC.college_day = TS.school_day
WHERE college_day = DATE_FORMAT(NOW(),'%W')

---

Solution

Thanks 37stars, u r genious, thanx for the ideo of IFNULL,

i m writing OPTIMUM AND BEST QUERY

SELECT TC.owner_id,college_open_time AS collegeOpen,college_close_time AS 
    collegeClose, IFNULL(TIME_FORMAT(school_open_time, '%h:%i %p'),college_open_time) 
    AS schoolOpen,IFNULL(TIME_FORMAT(school_close_time, '%h:%i %p',college_close_time)
    AS schoolClose FROM tbl_college TC LEFT JOIN tbl_school TS 
    ON TS.owner_id = TC.owner_id AND TC.college_day = TS.school_day 
    WHERE college_day = DATE_FORMAT(NOW(),'%W') 
    FROM tbl_storecalendar TS LEFT JOIN tbl_delivery_hours TD 
    ON TD.store_id = TS.store_id 
    AND TD.del_day = TS.dayName WHERE dayName = DATE_FORMAT( NOW( ) , '%W' )

Answer 1

I would recommend changing your DB structure to two tables with the following structure:

table institutions:
institution | institution_id
table times:
institution_id | day | open_time | close_time


You could easily put your two existing tables into the new times table, i.e.
INSERT INTO times(institution, day, open_time, close_time)
SELECT 'School', school_day, school_open_time, school_close_time
FROM table_school


And then getting the query results is easy:
SELECT i.institution, t.open_time, t.close_time
FROM times t
LEFT JOIN institutions i on t.institution_id=i.institution_id
WHERE day='Wednesday'


Answer 2

If you want to accomplish this in one query, you'll have to use UNION to put your results together, like this:

SELECT school_open_time AS openTime,`int` AS school_close_time
    FROM table_school WHERE school_day=DATE_FORMAT(NOW(),'%W')
UNION
SELECT college_open_time AS openTime,`int` AS college_close_time
    FROM table_college WHERE college_day=DATE_FORMAT(NOW(),'%W');

Where DATE_FORMAT(NOW(),'%W') converts to the current day of the week.

However, this method doesn't seem all that great to me. First of all, it won't handle putting NULL methods into the result set for you in the case of a missing record. You could add some IF/ELSE statements to do this for you, but in all honesty, I'd probably just make two separate queries from your PHP code. That way, you can handle each one specifically and control your results more easily from there.

Answer 3

You want a FULL OUTER JOIN, but unfortunately MySQL doesn't support this. Luckily, there is a workaround by combining a left join and a right join using UNION ALL:

Update: Changed query to answer OP's updated question.

SELECT
    COALESCE(school_day, college_day) AS day,
    COALESCE(school_open_time, college_open_time) AS school_open_time,
    COALESCE(school_close_time, college_close_time) AS school_close_time,
    COALESCE(college_open_time, school_open_time) AS college_open_time,
    COALESCE(college_close_time, school_close_time) AS college_close_time
FROM (
    SELECT * FROM table_school LEFT JOIN table_college ON school_day = college_day
    UNION ALL
    SELECT * FROM table_school RIGHT JOIN table_college ON school_day = college_day
    WHERE school_day IS NULL
) AS T1

Answer 4

You need to add school_day = college_day to your JOIN clause.

SELECT college_open_time, college_close_time, school_open_time, school_close_time
FROM dbo.tbl_college AS TC
    LEFT JOIN dbo.tbl_school AS TS ON TS.owner_id = TC.owner_id 
    AND TS.school_day = TC.college_day
WHERE TC.owner_id = 1 
    AND college_day = 'tuesday'