MYSQL left join and inner join

338
January 05, 2017, at 07:29 AM

i have 3 tables

Employee => e_id (P.K), dep_id (F.k)

Department => d_id (P.K)

Attendance => id (P.K), date_of_absence (DATE), e_id_f (F.K)

i want to get the number of absent employees in certain department for a certain period.

i created a table called MyDates

CREATE TABLE MyDates (mydate date);

and this procedure to fill MyDates Table with Dates

CREATE PROCEDURE filldates(dateStart DATE, dateEnd DATE)

BEGIN

  WHILE dateStart <= dateEnd DO
    INSERT INTO MyDates (mydate) VALUES (dateStart);
    SET dateStart = date_add(dateStart, INTERVAL 1 DAY);
  END WHILE;
END;

then i called the procedure to fill MyDates with date CALL filldates('2017-01-01','2017-03-31');

Then i made this select statement :

select mydates.mydate, count(attendance.date_of_absence)
from mydates left join attendance on
mydates.mydate = attendance.date_of_absence
where mydates.mydate Between "2017-01-01" AND "2017-01-31"
group by mydates.mydate

This query gets what i need but for all departments, BUT for a certain department the number of rows is incorrect

select mydates.mydate, count(attendance.date_of_absence)
from mydates
left join attendance on mydates.mydate = attendance.date_of_absence
inner join employee on attendance.e_id_f = employee.e_id
where mydates.mydate Between "2017-01-01" AND "2017-01-31" AND employee.dep_id = 4
group by mydates.mydate;

This is a screenshot IMG

Answer 1

There are 3 issues with your query I can see. The 1st 2 results in dropping the dates where no matching record is found, the 3rd potentially means that you get wrong counts:

  1. inner join employee on attendance.e_id_f = employee.e_id - if there is no matching record for a day, then attendance.e_id_f will be null for that day. The inner join will eliminate this record from the resultset. Solution: make this join a left as well.

  2. AND employee.dep_id = 4 - this would only keep records in the resultset, that have an employee record associated with it, so if you have a day with 0 absences, this criteria would eliminate it from the resultset. Solution: include this criteria in the join condition.

  3. count(attendance.date_of_absence) - counts occurances from the attendance table, which will not be correct after adding the 2nd left join. Solution: use count(employee.dep_id) instead.

Modified query:

select mydates.mydate, count(employee.dep_id)
from mydates
left join attendance on mydates.mydate = attendance.date_of_absence
left join employee on attendance.e_id_f = employee.e_id AND employee.dep_id = 4
where mydates.mydate Between "2017-01-01" AND "2017-01-31" group by mydates.mydate

Alternative solution is to use nested joins, where you specifically instruct MySQL to execute attendance inner join employee join first. You still need to move the employee.dep_id = 4 condition to the join condition:

select mydates.mydate, count(attendance.date_of_absence)
from mydates
left join (attendance inner join employee)
    on mydates.mydate = attendance.date_of_absence
       and attendance.e_id_f = employee.e_id
       and employee.dep_id = 4
where mydates.mydate Between "2017-01-01" AND "2017-01-31"
group by mydates.mydate

In the latter case the nested inner join ensures that only those attendance records are returned that belong to dep_id = 4 and these records are then left joined to your dates table. In this case there is no need to change the field you are counting.

READ ALSO
mysql UNION statement works weird

mysql UNION statement works weird

I have this select funtion in mysql, however it seems to work a bit weirdFirst off in phpmyadmin, if I run the first and second sperately it gives me 4 from the first select and 1 from the second

264
How to solve the issue of getting only the last row update?

How to solve the issue of getting only the last row update?

I'm trying to update multiple row into the database but everytime it could just update only the fourth row which is the $anstext4What could be the error from this ?

257
How to clone a javascript ES6 class instance

How to clone a javascript ES6 class instance

How do I clone a Javascript class instance using ES6

1003
How to connect a click (and drag) event to two elements simultaneously

How to connect a click (and drag) event to two elements simultaneously

I am building a web application that utilizes the -webkit-app-region: drag property on certain elementsOnly thing is -webkit-app-region: drag basically disables all functionality for that element apart from being able to drag

321