Select Mysql with empty value on join

Question

I have the following MySQL tables (create & insert):

CREATE TABLE IF NOT EXISTS `department` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  `father` INT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_department_department_idx` (`father` ASC) VISIBLE,
  CONSTRAINT `fk_department_department`
    FOREIGN KEY (`father`)
    REFERENCES `department` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `print` (
  `id` INT NOT NULL,
  `page` INT NOT NULL,
  `copy` INT NOT NULL,
  `date` DATE NOT NULL,
  `department` INT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_print_department1_idx` (`department` ASC) VISIBLE,
  CONSTRAINT `fk_print_department1`
    FOREIGN KEY (`department`)
    REFERENCES `department` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

insert into department (id,name,father)
values
(1, 'dp1',null),
(2, 'dp2',null),
(3, 'dp3',1),
(4, 'dp4',2);

insert into print (id,page,copy,date,department)
values
(1,2,3,'2020-1-11',1),
(2,1,6,'2020-1-12',4),
(3,1,6,'2020-1-12',1),
(4,4,5,'2020-1-13',null),
(5,5,3,'2020-1-15',null),
(6,3,4,'2020-1-15',1),
(7,3,4,'2020-1-15',1),
(8,2,2,'2020-1-16',4);

The closest I got was with the following query.

select
    d.name as department,
    f.name as father,
    sum_print as sum
from
    department d
left join department f on f.id = d.father
left join
(
  select 
    department,
    sum(page*copy) as sum_print
  from print
  where date between CAST('2020-1-13' AS DATE) 
                        AND    CAST('2020-1-15' AS DATE)
  group by department
) as tmp on tmp.department = d.id;

The result I got was.

|  department  |  father  |  sum  |
|     dp1      |   null   |  24   |
|     dp2      |   null   | null  |
|     dp3      |   dp1    | null  |
|     dp4      |   dp2    | null  |

what i need is the following.

|  department  |  father  |  sum  |
|     dp1      |   null   |  24   |
|     dp2      |   null   | null  |
|     dp3      |   dp1    | null  |
|     dp4      |   dp2    | null  |
|     null     |   null   | 35    |

For some reason that I can't figure out, I'm missing the last tuple. I think it's because of this join as tmp on tmp.department = d.id

Follow the fiddle for anyone who wants to use it and / or can help: Fiddle

I tried the following posts but no luck. Im stuck now.

• mysql-select-join-with-empty-row-returns
• mysql-inner-join-with-possible-empty-fields
• mysql-how-to-return-rows-even-if-join-data-is-empty
• how-to-join-tables-in-mysql-with-empty-table

Answer 1

What you actually need is a full outer join of 2 queries.
The problem is that MySql (still) does not support this kind of join, so it must be emulated with a left and a right join and union all:

with 
  d as (
    select d.id, d.name as department, f.name as father
    from department d left join department f 
    on f.id = d.father  
  ),
  p as (
    select department, sum(page*copy) as sum_print
    from print
    where date between CAST('2020-1-13' AS DATE) AND CAST('2020-1-15' AS DATE)
    group by department  
  )
select d.department, d.father, p.sum_print
from d left join p
on p.department = d.id
union all
select d.department, d.father, p.sum_print
from d right join p
on p.department = d.id
where d.id is null

See the demo.
Results:

> department | father | sum_print
> :--------- | :----- | --------:
> dp1        | null   |        24
> dp2        | null   |      null
> dp3        | dp1    |      null
> dp4        | dp2    |      null
> null       | null   |        35

Answer 2

This will give you the desired result with outer join

select
    d.name as department,
    f.name as father,
    sum_print as sum
from
    department d
left join department f on f.id = d.father
left join
(
  select 
    department,
    sum(page*copy) as sum_print
  from print
  where date between CAST('2020-1-13' AS DATE) 
                        AND    CAST('2020-1-15' AS DATE)
  group by department
) as tmp on tmp.department = d.id
UNION
select
    d.name as department,
    f.name as father,
    sum_print as sum
from
    department d
left join department f on f.id = d.father
right join
(
  select 
    department,
    sum(page*copy) as sum_print
  from print
  where date between CAST('2020-1-13' AS DATE) 
                        AND    CAST('2020-1-15' AS DATE)
  group by department
) as tmp on tmp.department = d.id;

https://www.db-fiddle.com/f/xvfhaQcFQMS8BF1W9iYcn2/1