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

i have 3 table this:

members_tbl id | fullname | email | mobileno  attendance_in_tbl  id | member_id | datetimein  attendance_out_tbl id | member_id | datetime_out 

i want select members date: 2014-03-10 query:

select attendance_in.edatetime, members_info.mfullname, attendance_out.odatetime attendance_in left join members_info on members_info.id = attendance_in.memid left join attendance_out on attendance_out.memid = attendance_in.memid date(attendance_in.edatetime) or date(attendance_out.odatetime) = "2014-03-10" 

but give me different results in attendace_out results

you have mistake in query.

you wrote:

 date(attendance_in.edatetime)   /* wrong! */     or date(attendance_out.odatetime) = "2014-03-10" 

this wrong, first expression date(attendance_in.edatetime) evaluates true.

you may want

where date(attendance_in.edatetime) = "2014-03-10"    or date(attendance_out.odatetime) = "2014-03-10" 

but, guaranteed perform poorly when attendance_in , attendance_out tables large, because have scan them; can't use index.

you may find performs better write this:

where (attendance_in.edatetime  >='2014-03-10' ,        attendance_in.edatetime  < '2014-03-10' + interval 1 day)    or (attendance_out.edatetime >='2014-03-10' ,        attendance_out.edatetime < '2014-03-10' + interval 1 day) 

that check whether either checkin our checkout time occurs on day in question.