How to use inner join on MySQL table?
Hi I have tables like this:
Employee
EmployeeID EmployeeName DepartementName
1234 Seulong Accounting
1235 Jokwon Accounting
1236 Jinwoon IT
1237 Changmin IT
1238 Junho IT
1239 Taecyeon IT
Shiftsceduling
(d as date, example d01 = date 1, d02 = date 2, etc.)
employeeID_shift month d01 d02 d03 d04 d05 d06 d07 d08 d09 d10
1234 7 1 1 2 3 0 1 1 2 1 2
1235 7 1 2 1 2 0 1 2 3 0 1
1236 7 1 2 3 0 2 1 1 1 1 1
1237 7 1 3 1 1 1 0 1 1 0 1
1238 7 0 2 1 3 2 1 1 2 1 1
1239 7 1 1 1 1 1 1 0 0 2 1
ShiftCode
idshift start end information
0 00:00:00 00:00:00 OFF
1 08:00:00 16:00:00 8am - 16pm
2 09:00:00 18:00:00 9am - 18pm
3 16:00:00 04:00:00 20pm - 04am
car
PoliceNumber EmployeeID_car
J 0115 JYP 1234
J 0634 JYP 1235
J 1227 JYP 1236
J 0430 JYP 1237
J 0125 JYP 1238
J 0211 JYP 1239
I have one function to blow up today's date:
$date=date("Y-m-d");
$a = $date;
{
$b = explode('-', $a);
$year = $b[0];
$bulan = $b[1];
$date2 = $b[2];
}
echo $date2;
I want to find the ID number of an employee in the IT department who is or is working on July 2 at 09:00 - 17:00 and also displays which machine they are using. I used this code and got confused with continuing to find the timing of their switch schedule:
SELECT Employee.* , Shiftsceduling.*, car.*, ShiftCode.*
FROM Employee
INNER JOIN Shiftsceduling ON Employee.EmployeeID = Shiftsceduling.employeeID_shift
INNER JOIN car ON Employee.EmployeeID = car.EmployeeID_car
WHERE Employee.DepartementName = 'IT' AND d'.$date2.' = '2'
AND ShiftCode.start <= '$starttime' AND ShiftCode.end >= '$endtime'
but it won't work, maybe you know where the problem is? thank you for your help
source to share
I think the main problem is that you are not joining the Shiftcode table. But you also seem to be confusing yourself by double checking the time range.
You seem to have a validation of the actual time ranges in the WHERE clause where you are checking the start and end. But you also check that the d01 / d02 / etc field from Shiftsceduling is 2 (this is the time period from 09:00 to 18:00).
Not tested, but I think you want something like this: -
SELECT Employee.* ,
Shiftsceduling.*,
car.*,
ShiftCode.*
FROM Employee
INNER JOIN Shiftsceduling ON Employee.EmployeeID = Shiftsceduling.employeeID_shift
INNER JOIN car ON Employee.EmployeeID = car.EmployeeID_car
INNER JOIN ShiftCode ON ShiftCode.idshift = Shiftsceduling.d'.$date2.'
WHERE Employee.DepartementName = 'IT'
AND ShiftCode.start <= '$starttime'
AND ShiftCode.end >= '$endtime'
source to share