Umer Best Umer Best - 27 days ago 8
MySQL Question

mysql select equal data with same date wise

I'm trying to run a mysql join query fetching data from 3 tables.
Table 1: user
Table 2: Outtiming
Table 3: Intiming

I want to show same date data from outtiming and intiming tables with same dates.

e.g: 2017-09-13 = 2017-09-13

But I'm not getting data with same dates.




$query = "SELECT ur.username, ur.user_department, it.*, ot.*
FROM users ur
INNER JOIN intiming it ON ur.staff_id=it.staff_id
INNER JOIN outtiming ot ON ur.staff_id=ot.staff_id
WHERE it.staff_id=".$employee."
AND it.date >= '$startDate' AND ot.date <= '$endDate'";


Result

mysql query result

Answer Source

Add an addition condition in your WHERE statement to check matching dates: it.date = ot.date . Full query:

$query = "SELECT ur.username, ur.user_department, it.*, ot.*
FROM users ur 
INNER JOIN intiming it ON ur.staff_id=it.staff_id 
INNER JOIN outtiming ot ON ur.staff_id=ot.staff_id 
WHERE it.staff_id=".$employee." 
AND it.date >= '$startDate' AND ot.date <= '$endDate'
AND it.date = ot.date ";