CODEWITHSUNDEEP
phpmysqlxamppinner-join
TARA
TARA

Reputation: 527

How to use inner join on a MySQL table?

Hello 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 explode 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 search all employee ID in IT departemen who available or work at July 2nd at 09:00 - 17:00 and also display what car they use. I have used this code and got confused with the continuation to search the time of their shift 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 does not work, may you know where is the problem? thank you for your help

Upvotes: 3

Views: 74

Answers (1)

Kickstart
Kickstart

Reputation: 21523

I think the main problem is that you are not joining on to the Shiftcode table. But you also appear to be confusing yourself by checking the time band twice.

You appear to have a check for the time band actual times in the WHERE clause where you check against start and end. But you also check that the d01 / d02 / etc field from Shiftsceduling is 2 (which is the 09:00 to 18:00 time band).

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'

Upvotes: 1

Related Questions