MYSQL 8.0: how to bring the result null or zero, if there is no activity in the informed date range?-CodePudding

Business rule: Calculate the printing volume of the department of the user informed (in this case, this is the user authenticated in the application), even if there is no printing.

Note 1: The query should return the print volume, the department name, as well as the parent department name.

Note 2: If the informed user is not linked to any department, you should consider 'no department' as a department and calculate his print volume.

MySQL Version 8.0 Schema SQL

CREATE TABLE IF NOT EXISTS `departments` (
  `id` BIGINT(20) UNSIGNED NOT NULL,
  `parent_department` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `departments_parent_department_foreign` (`parent_department` ASC),
  CONSTRAINT `departments_parent_department_foreign`
    FOREIGN KEY (`parent_department`)
    REFERENCES `departments` (`id`)
    ON UPDATE CASCADE)
ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `users` (
  `id` BIGINT(20) UNSIGNED NOT NULL,
  `department_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
  `username` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `users_username_unique` (`username` ASC),
  INDEX `users_department_id_foreign` (`department_id` ASC),
  CONSTRAINT `users_department_id_foreign`
    FOREIGN KEY (`department_id`)
    REFERENCES `departments` (`id`)
    ON UPDATE CASCADE)
ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `prints` (
  `id` BIGINT(20) UNSIGNED NOT NULL,
  `department_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
  `user_id` BIGINT(20) UNSIGNED NOT NULL,
  `date` DATE NOT NULL,
  `pages` BIGINT(20) UNSIGNED NOT NULL,
  `copies` BIGINT(20) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `prints_department_id_foreign` (`department_id` ASC),
  INDEX `prints_user_id_foreign` (`user_id` ASC),
  CONSTRAINT `prints_department_id_foreign`
    FOREIGN KEY (`department_id`)
    REFERENCES `departments` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `prints_user_id_foreign`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

Dumb Data:

insert into departments (id,parent_department,name)
values
(1, null, 'dep 1'),
(2, null, 'dep 2'),
(3, 1, 'dep 3'),
(4, 1, 'dep 4');

insert into users (id,department_id,username)
values
(1, null, 'user 1'),
(2, 1, 'user 2'),
(3, 2, 'user 3'),
(4, 3, 'user 4'),
(5, 4, 'user 5');

insert into prints (id,department_id,user_id,date,pages,copies)
values
(1,null,1,'2020-1-11',1,2),  # dep null, user 1, total 2
(2,null,1,'2020-1-12',3,2),  # dep null, user 1, total 6
(3,1,1,'2020-1-13',4,1),     # dep 1,    user 1, total 4

(4,null,2,'2020-1-13',3,2),  # dep null, user 2, total 6
(5,2,2,'2020-1-15',2,2),     # dep 2,    user 2, total 4
(6,2,2,'2020-1-16',5,1),     # dep 2,    user 2, total 5

(7,4,2,'2021-12-30',1,10);   # dep 5, user 2, total 10

Query so far

select
    SUM(i.copies * i.pages) AS total_print,
    d.name AS department,
    parent.name AS parent_department
from prints as i
right join departments as d on d.id = i.department_id
inner join users as u on u.department_id = d.id
left join departments as parent on parent.id = d.parent_department
where `u`.`username` = 'user 5'
and (i.date between cast('2020-1-10' as date) and cast('2020-1-17' as date) or i.date is null)
group by u.department_id;

Expected results when you enter the following users:

'user 1' expect: total_print 14, department null, parent_department null

Not working: As this user is not linked to any department, he should calculate the print volume of all 'null departments'. However, it does not return any results.

'user 2' expect: total_print 4, department 'dep 1', parent_department null

Working as expected.

'user 3' expect: total_print 9, department 'dep 2', parent_department null

Working as expected.

'user 4' expect: total_print null, department 'dep 3', parent_department 'dep 1'

Working as expected.

'user 5' expect: total_print null, department 'dep 4', parent_department 'dep 1'

Not working. In this case, the query stops working, because the informed department has printing outside the informed date range. I have no idea why this behavior is happening.

Fiddle: https://www.db-fiddle.com/f/jFEN43Ecq4J58AdKpfcWhF/1

Thx for your help.

CodePudding user response：

In this case, the query stops working, because the informed department has printing outside the informed date range. I have no idea why this behavior is happening.

Since print is part of an OUTER join, using the date column in the WHERE clause implicitly converts the join into an INNER JOIN, which is why the query isn't returning any results.

Before the date filter is applied, the query returns this result:

copies	pages	department	parent_department	username	date
10	1	dep 4	dep 1	user 5	2021-12-30

The database then applies the date filter to those results:

WHERE ... 
 (  i.date BETWEEN cast('2020-1-10' as date) AND cast('2020-1-17' as date)  
    OR i.date is null
 )

Since date is obviously not NULL, nor does 2020-12-30 fall between the date range 2020-01-10 and 2020-01-17, the row is dropped, and the query returns nothing. The solution is move the date filter into the JOIN.

That said, mixing right and left joins is not intuitive at all, imo. It makes it harder for someone reviewing the sql to understand the expected result. Personally, I'd recommend rewriting the query to use only LEFT JOIN's rather than a mix of the two.

Query:

SELECT
        SUM(i.copies * i.pages) AS total_print
        , d.name AS department
        , parent.name AS parent_department
FROM    departments d 
            INNER JOIN users as u ON u.department_id = d.id
            LEFT JOIN departments as parent ON parent.id = d.parent_department
            LEFT JOIN prints as i ON d.id = i.department_id
                 AND i.`date` >= '2020-01-10'
                 AND i.`date` <= '2020-01-17'
WHERE u.username = 'user 5'
GROUP BY u.department_id;

Results:

total_print	department	parent_department
null	dep 4	dep 1

db<>fiddle here

CodePudding user response：

For your query and structure, we need to be creative.

As you need an department to link user and prints, we need to create a dummy department in your case 0

Now we can replace all NULL departments in prints and users with this 0 in the query, so that the link can be made.

At last we need to remove the dummy department.

Still, as you have a recursive structure in departmnents and you are ussig MySQL 8 bso take a look at Recursive CTE

CREATE TABLE IF NOT EXISTS `departments` (
  `id` BIGINT(20) UNSIGNED NOT NULL,
  `parent_department` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
  `name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `departments_parent_department_foreign` (`parent_department` ASC),
  CONSTRAINT `departments_parent_department_foreign`
    FOREIGN KEY (`parent_department`)
    REFERENCES `departments` (`id`)
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `users` (
  `id` BIGINT(20) UNSIGNED NOT NULL,
  `department_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
  `username` VARCHAR(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `users_username_unique` (`username` ASC),
  INDEX `users_department_id_foreign` (`department_id` ASC),
  CONSTRAINT `users_department_id_foreign`
    FOREIGN KEY (`department_id`)
    REFERENCES `departments` (`id`)
    ON UPDATE CASCADE)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `prints` (
  `id` BIGINT(20) UNSIGNED NOT NULL,
  `department_id` BIGINT(20) UNSIGNED NULL DEFAULT NULL,
  `user_id` BIGINT(20) UNSIGNED NOT NULL,
  `date` DATE NOT NULL,
  `pages` BIGINT(20) UNSIGNED NOT NULL,
  `copies` BIGINT(20) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `prints_department_id_foreign` (`department_id` ASC),
  INDEX `prints_user_id_foreign` (`user_id` ASC),
  CONSTRAINT `prints_department_id_foreign`
    FOREIGN KEY (`department_id`)
    REFERENCES `departments` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `prints_user_id_foreign`
    FOREIGN KEY (`user_id`)
    REFERENCES `users` (`id`)
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB;

insert into departments (id,parent_department,name)
values
(1, null, 'dep 1'),
(2, null, 'dep 2'),
(3, 1, 'dep 3'),
(4, 1, 'dep 4'),
(0,NULL,'dummy');


insert into users (id,department_id,username)
values
(1, null, 'user 1'),
(2, 1, 'user 2'),
(3, 2, 'user 3'),
(4, 3, 'user 4'),
(5, 4, 'user 5');

insert into prints (id,department_id,user_id,date,pages,copies)
values
(1,null,1,'2020-1-11',1,2),  # dep null, user 1, total 2
(2,null,1,'2020-1-12',3,2),  # dep null, user 1, total 6
(3,1,1,'2020-1-13',4,1),     # dep 1,    user 1, total 4

(4,null,2,'2020-1-13',3,2),  # dep null, user 2, total 6
(5,2,2,'2020-1-15',2,2),     # dep 2,    user 2, total 4
(6,2,2,'2020-1-16',5,1),     # dep 2,    user 2, total 5

(7,4,2,'2021-12-30',1,10);   # dep 5, user 2, total 10

select
    SUM(i.copies * i.pages) AS total_print,
    IF(d.name = 'dummy', NULL,d.name) AS department,
    parent.name AS parent_department
from (SELECT id,IFNULL(department_id,0) AS department_id ,user_id,date,pages,copies FROM prints) as i
RIGHT join departments as d on d.id = i.department_id
INNER join (SELECT id,IFNULL(department_id, 0) department_id ,username FROM users) as u on u.department_id = d.id
left join departments as parent on parent.id = d.parent_department
where `u`.`username` = 'user 1'
and (i.date between cast('2020-1-10' as date) and cast('2020-1-17' as date) or i.date is null)
group by u.department_id;

total_print | department | parent_department
----------: | :--------- | :----------------
         14 | null       | null

select
    SUM(i.copies * i.pages) AS total_print,
    IF(d.name = 'dummy', NULL,d.name) AS department,
    parent.name AS parent_department
from (SELECT id,IFNULL(department_id,0) AS department_id ,user_id,date,pages,copies FROM prints) as i
RIGHT join departments as d on d.id = i.department_id
INNER join (SELECT id,IFNULL(department_id, 0) department_id ,username FROM users) as u on u.department_id = d.id
left join departments as parent on parent.id = d.parent_department
where `u`.`username` = 'user 2'
and (i.date between cast('2020-1-10' as date) and cast('2020-1-17' as date) or i.date is null)
group by u.department_id;

total_print | department | parent_department
----------: | :--------- | :----------------
          4 | dep 1      | null

select
    SUM(i.copies * i.pages) AS total_print,
    IF(d.name = 'dummy', NULL,d.name) AS department,
    parent.name AS parent_department
from (SELECT id,IFNULL(department_id,0) AS department_id ,user_id,date,pages,copies FROM prints) as i
RIGHT join departments as d on d.id = i.department_id
INNER join (SELECT id,IFNULL(department_id, 0) department_id ,username FROM users) as u on u.department_id = d.id
left join departments as parent on parent.id = d.parent_department
where `u`.`username` = 'user 3'
and (i.date between cast('2020-1-10' as date) and cast('2020-1-17' as date) or i.date is null)
group by u.department_id;

total_print | department | parent_department
----------: | :--------- | :----------------
          9 | dep 2      | null

db<>fiddle here