Filter Table And Left Join

August 23, 2022

I have one table "tasks" which contains two types of tasks (validation and import tasks). The validation task and the import task will process documents (document id is the the doc_id column).

tasks

id
doc_id
type

Sample of the data

id|type      |doc_id|
--+----------+------+
 1|others    |      |
 2|validation|     7|
 3|import    |     7|
 4|others    |      |
 5|validation|     8|
 6|import    |     8|
 7|validation|     9|

I would like to join tasks table with itself, the join condition will be the doc_id.
I need to display all validation tasks even the import tasks has not been created yet.
The expected result should be:

val_task          imp_task      doc_id
2                   3             7
5                   6             8
7                   NULL          9

I tried to use left join. My query is something like

select val.type,
       imp.type,
       val.id as val_task_id,
       imp.id as imp_task_id,
       val.doc_id as doc_id
FROM tasks val
left join tasks imp
on val.doc_id=  imp.doc_id
where (val.type='validation' and imp.type='import')

It does not return the validation task with id 7. The actual result is

val_task          imp_task      doc_id
2                   3             7
5                   6             8

Can anyone help me to correct this query please?
Thank you in advance,

>Solution :

By filtering in the where you effectively have an inner join, the type should be part of the join criteria:

select val.type,
       imp.type,
       val.id as val_task_id,
       imp.id as imp_task_id,
       val.doc_id as doc_id
FROM tasks val
left join tasks imp on val.doc_id = imp.doc_id and imp.type = 'import'
where val.type = 'validation';