Specific join between tables

Question

I have two tables

customers

id	razao_social
1	company_1
2	company_2

and tasks

id	id_client	tasks	competency
1	1	'101'	'032021'
2	1	'102'	'032021'
3	2	'101'	'032021'

When I make one join between these tables, I get a correct result, but it’s NOT what I’m looking for:

select c.razao_social , s.tarefas , s.competencia 
from clientes c
join tarefas s on c.id = s.id_cliente
 where s.competencia = '032021'
 order by s.id_cliente;

Result obtained:

razao_social	tasks	competency
company_1	'101'	'032021'
company_1	'102'	'032021'
company_2	'101'	'032021'

Result I would like to get:

razao_social	tasks	competency
company_1	'101'	'032021'
company_1	'102'	'032021'
company_2	'101'	'032021'
company_2	null or empty	'032021'

(or anything else indicating that the task '102' was not made for company_2)

Is there a query that helps me generate this table result? I can (and am) using the programming language to correct this "missing line", but I wanted to know if there is another way, at query level SQL.

Tested: apply left join or right join were insufficient to do what I want.

Answer 1

You can use nested consultations, following the next two steps:
First: A consultation should be made that results in a preliminary set that I will call groundwork of the desired information;
According to: Left Join with the tables that were used to build the base.

First step - Get base table:

select distinct id_cliente, t2.tarefa, t.competencia  from tarefas t, 
    (select distinct tarefas as tarefa, competencia  from tarefas) t2
      where t.competencia = t2.competencia 
        and t.competencia = '032021'  
      order by id_cliente, t2.tarefa   

Upshot - Base table:

id_client	task	competency
1	101	032021
1	102	032021
2	101	032021
2	102	032021

This way we get all customers combined with all tasks and the field competencia.

Second step: Using the base table with Left Join:
Having obtained the base table of the combinations, now just do the Left Join, let’s see:

select c.razao_social, t3.tarefas, base.competencia from ( 
    select distinct id_cliente, t2.tarefa, t.competencia  from tarefas t, 
        (select distinct tarefas as tarefa, competencia  from tarefas) t2
     where t.competencia = t2.competencia 
       and t.competencia = '032021'  
     order by id_cliente, t2.tarefa 
) base left join clientes c on base.id_cliente = c.id 
       left join tarefas t3 on base.id_cliente = t3.id_cliente 
                           and base.tarefa = t3.tarefas 
                           and base.competencia = t3.competencia  

Result obtained:

razao_social	tasks	competency
company_1	101	032021
company_1	102	032021
company_2	101	032021
company_2	[NULL]	032021

This way it is possible to obtain the desired result.