0
CREATE TABLE `EmpWorkCount` (
      `emp_Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `day` int(10) unsigned NOT NUL,
      `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `updated_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `daycount` int(10) NOT NULL DEFAULT '0',
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Current Values in the table :

    emp_Id    |       day            | daycount  |
       1      |        0             |  10       |
       1      |        1             |  11       | 
       2      |        1             |  13       | 
       2      |        0             |  12       | 

.

CREATE TABLE `Emp` (
      `emp_Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `created_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
      `updated_At` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Emp Table looks like :

emp_Id    |       created_At            | updated_At            |
   1      |        2018-12-04 20:00:00  |  2018-12-04 20:00:00  |
   1      |        2018-12-04 20:00:00  |  2018-12-04 20:00:00  | 
   3      |        2018-12-04 20:00:00  |  2018-12-04 20:00:00  |

I need to run a query where I need a count of EmpWorkCount with different day values (0, 1, 2, 3) for each employee

    Select emp_Id, day_list.day, 
    IF(daycount = NULL, 0, daycount)  as daycount from (
        SELECT 0 day
           UNION ALL
           SELECT 1
           UNION ALL
           SELECT 2
           UNION ALL
           SELECT 3   )day_list 
      LEFT JOIN 
      select emp_Id, day, daycount from EmpWorkCount
   )   

(my query is incorrect, but I don't know how I multiply these 4 values from day for each employee

this gives output for the emp_Id, day values present in the table e.g.

emp_Id    |       day            | daycount    |
   1      |        0             |  10         |
   1      |        1             |  11         | 
   2      |        1             |  13         |
   2      |        0             |  12         | 
   NULL   |        2             |  0          | 
   NULL   |        3             |  0          | 

but if the database does not contain let's say emp_Id : 1 with day values = 2 or 3 these records don't appear with a count 0.

Output that I look for :

    emp_Id    |       day            | daycount    |
       1      |        0             |  10         |
       1      |        1             |  11         | 
       1      |        2             |  0          |
       1      |        3             |  0          | 
       2      |        1             |  13         |
       2      |        0             |  12         | 
       2      |        2             |  0          | 
       2      |        3             |  0          | 
       3      |        0             |  0          |
       3      |        1             |  0          | 
       3      |        2             |  0          | 
       3      |        3             |  0          | 

How do I go about this one?

In my query I have not joined with Employee Table --> but the output should even contain Employees which do not have a entry in EmpWorkCount Table.

1

You must generate all possible (employee, day) pairs, so you must obtain employees list additionally, not only days list.

The query will be something like

SELECT emp_list.emp_Id, day_list.day, COALESCE(EmpWorkCount.daycount, 0) daycount
FROM ( SELECT 0 day
       UNION ALL
       SELECT 1
       UNION ALL
       SELECT 2
       UNION ALL
       SELECT 3 ) day_list 
JOIN ( SELECT emp_Id FROM EmpWorkCount 
       UNION 
       SELECT emp_Id FROM Emp ) emp_list
LEFT JOIN EmpWorkCount ON day_list.day = EmpWorkCount.day
                      AND emp_list.emp_Id = EmpWorkCount.emp_Id
/* ORDER BY 1, 2 */
| improve this answer | |
  • Thank you, Akina. Don't you think SELECT DISTINCT emp_Id FROM EmpWorkCount should be from Emp table. – j10 Dec 4 '18 at 10:03
  • @j10 As I see it's possible emp_id exists in one table and not exists on another and vice versa. So maybe we need to collect epmployees from both tables. Thank you! Answer corrected. – Akina Dec 4 '18 at 10:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.