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.

Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. — philipxy, Oct 03 '20 at 07:35
Does this answer your question? [Left Outer Join doesn't return all rows from my left table?](https://stackoverflow.com/questions/4707673/left-outer-join-doesnt-return-all-rows-from-my-left-table) — philipxy, Oct 03 '20 at 07:38
Please clarify via edits, not comments. One problem here is misuse of left join, see the link. Saying posts didn't help is not useful, say what you tried & explain how it didn't help. Please don't expect us to guess from one example. Use words to say what you want. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. When giving a business relation(ship)/association or table (base or query result), say what a row in it states about the business situation in terms of its column values. — philipxy, Oct 03 '20 at 07:42

score 1 · Accepted Answer · answered Oct 03 '20 at 06:31

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

score 1 · Answer 2 · answered Oct 03 '20 at 06:32

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

@forpas and aRvi I think I am committing an injustice since there is no way I can say that one answer helped me more than the other. Both explained the problem to me and pointed out the solution. I will choose that one because of the seconds faster than it was, although this is not a fair reason. Anyway, thank you both. — Fábio, Oct 03 '20 at 06:56

Select Mysql with empty value on join

2 Answers2