How to join three tables and set blank fields to null?-CodePudding

I used full join and left join to join the Person, Tasks and Task tables. The result shown on the screen resulted in a number of lines greater than six. Unset fields have the value NULL and that's good.

The expected output can be obtained using such joins, however it is necessary to use clauses that allow the common fields to be joined. How can I do this?

CodePudding user response：

A CROSS JOIN produces all the combinations you want. Then a simple outer join can retrieve the related rows (should they exist).

You don't mention the database you are using so a faily standard query will do. For example (in PostgreSQL):

select
  row_number() over(order by p.id, t.id) as id,
  p.name,
  case when x.st is not null then t.hr end,
  x.st
from person p
cross join tasks t
left join task x on x.personid_fk = p.id and x.taskid_fk = t.id
order by p.id, t.id;

Result:

 id  name  case   st    
 --- ----- ------ ----- 
 1   Anna  null   null  
 2   Anna  null   null  
 3   Luo   13:00  true  
 4   Luo   14:00  false 
 5   John  null   null  
 6   John  null   null

See running example at DB Fiddle.