Filter Table And Left Join-CodePudding

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,

CodePudding user response：

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';