CODEWITHSUNDEEP
phpmysqlsql
user3149421
user3149421

Reputation: 1

MySQL Select from 3 tables and get attendance In and Out Time for all Members in specific date

I have three table Like this:

members_tbl
id | Fullname | Email | MobileNo

attendance_in_tbl 
id | member_id | DateTimeIN

attendance_out_tbl
id | member_id | DateTime_OUT

I want to select all members for date: 2014-03-10 by this query:

SELECT
attendance_in.EDatetime,
members_info.mfullname,
attendance_out.ODatetime
FROM
attendance_in
LEFT JOIN members_info ON members_info.id = attendance_in.MemID
LEFT JOIN attendance_out ON attendance_out.MemID = attendance_in.MemID
WHERE date(attendance_in.EDatetime) OR date(attendance_out.ODatetime) = "2014-03-10"

But it give me different results in Attendace_out Results

Upvotes: 0

Views: 156

Answers (1)

O. Jones
O. Jones

Reputation: 108841

You have a mistake in your query.

You wrote: 

 WHERE date(attendance_in.EDatetime)   /* wrong! */
    OR date(attendance_out.ODatetime) = "2014-03-10"

This is wrong, as the first expression date(attendance_in.EDatetime) always evaluates to true.

You may want 

WHERE date(attendance_in.EDatetime) = "2014-03-10"
   OR date(attendance_out.ODatetime) = "2014-03-10"

But, this is guaranteed to perform poorly when your attendance_in and attendance_out tables get large, because it will have to scan them; it can't use an index.

You may find that it performs better to write this:

WHERE (attendance_in.EDatetime  >='2014-03-10' AND
       attendance_in.EDatetime  < '2014-03-10' + INTERVAL 1 DAY)
   OR (attendance_out.EDatetime >='2014-03-10' AND
       attendance_out.EDatetime < '2014-03-10' + INTERVAL 1 DAY)

That will check whether either the checkin our checkout time occurs on the day in question.

Upvotes: 1

Related Questions