StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POhow to compare two tables fields name with another value in mysql?
text
Body
copied!<p>I have two tables</p> <ol> <li><p>table_school</p> <pre><code>school_open_time|school_close_time|school_day 8:00 AM | 9:00PM | Monday 10:00 AM | 7:00PM | Wednesday </code></pre></li> <li><p>table_college</p> <pre><code> 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 </code></pre></li> </ol> <p>Now I want to select <code>school_open_time</code> <code>school_close time</code>, <code>college_open_time</code> and <code>college_close_time</code> according to today (means <code>college_day=school_day=today</code>), and also if there is no row for a specific day in any of one table then it display blank field ( <code>LEFT JOIN</code> , I think I can use).</p> <p>Please suggest me best and optimized query for this.</p> <p><strong>UPDATE:</strong></p> <p>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</p> <hr> <p>i m using below query </p> <pre><code> 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())."'"; </code></pre> <p>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)</p> <pre><code>i need only one row when both table have a row of a given day </code></pre> <p>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</p> <hr> <p><strong>More UPDATE @37Stars</strong></p> <p>There is a little bit problem also Dear, datatype of school_close_time and school_open time is <code>TIME</code> type whereas datatype of college_open_time and college_close_time is <code>VARCHAR</code> type. i used below code given by you but i modified a bit and i m getting close to result,</p> <p>but now tell me where i have to write IFNULL in below code segment</p> <p><strong><em>IFNULL(TIME_FORMAT()) Or TIME_FORMAT(IFNULL())</em></strong></p> <pre><code>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') </code></pre> <hr> <p><strong>Solution</strong></p> <p>Thanks 37stars, u r genious, thanx for the ideo of IFNULL,</p> <p><em>i m writing OPTIMUM AND BEST QUERY</em></p> <pre><code>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' ) </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload