How to execute multiple joins with different params in a single query

I have two tables, question(question_id) and question_exclusion(question_type, question_sub_type, question_id)

I’m able to do it If I specify question_type and question_sub_type.

SELECT *
FROM question AS t1
         LEFT JOIN (SELECT t.question_id
                    FROM question_exclusion as t
                    WHERE t.question_type = 'A'
                      AND t.question_sub_type = 'A_1') AS t2
                   ON t1.question_id = t2.question_id
WHERE t2.question_id is null;

But what I want to achieve is to get all questions with questions_ids in a single query for all possible question_type and questions_sub_type

question_type and questions_sub_type are dynamic parameters I don’t know exact values until query execution

Update 1:

actual data:

table: question

question_id|
42
10
2
36
49

table: question_exclusion

question_type|question_sub_type|question_id|
A            | A_1             | 42
A            | A_1             | 10
A            | A_2             | 10
B            | B_1             | 36 
C            | null            | 2

expected result:

question_type|question_sub_type|question_id
A            | A_1             | 2
A            | A_1             | 36
A            | A_1             | 49
A            | A_2             | 42
A            | A_2             | 2
A            | A_2             | 36
A            | A_2             | 49
B            | B_1             | 42
B            | B_1             | 10
B            | B_1             | 2
B            | B_1             | 49
C            | null            | 42
C            | null            | 10
C            | null            | 36
C            | null            | 49

its like a list of lists for each type and sub_type combination taking into consideration exclusion table

For example:

type=A, sub_type=A_1 -> (select * from questions) - (select * from question_exclusion where type='A' and sub_type='A_1')
+
type=A, sub_type=A_2 -> (select * from questions) - (select * from question_exclusion where type='A' and sub_type='A_2')
+
type=B, sub_type=B_1 -> (select * from questions) - (select * from question_exclusion where type='B' and sub_type='B_2')

Of course I can query all distinct (type, sub_type) and make another query by combining with union

SELECT *
FROM question AS t1
         LEFT JOIN (SELECT t.question_id
                    FROM question_exclusion as t
                    WHERE t.question_type = 'A'
                      AND t.question_sub_type = 'A_1') AS t2
                   ON t1.question_id = t2.question_id
WHERE t2.question_id is null
UNION
SELECT *
FROM question AS t1
         LEFT JOIN (SELECT t.question_id
                    FROM question_exclusion as t
                    WHERE t.question_type = 'B'
                      AND t.question_sub_type = 'B_1') AS t2
                   ON t1.question_id = t2.question_id
WHERE t2.question_id is null
...
...
N times for all type and sub_type

I’m looking for another solid way of doing this in a single query

Answer

I add row (A | A_1 | 10) into question_exclusion in your data for more clean solution:

with question(question_id) as (
    select 42 union all
    select 10 union all
    select 2 union all
    select 36 union all
    select 49
),
question_exclusion(question_type, question_sub_type, question_id) as (
    select 'A', 'A_1', 42 union all
    select 'A', 'A_1', 10 union all
    select 'A', 'A_2', 10 union all
    select 'B', 'B_1', 36 union all 
    select 'C', null, 2
),
question_types(question_type, question_sub_type) as (
    select distinct question_type, question_sub_type from question_exclusion
)
select
    qt.question_type, qt.question_sub_type, q.question_id
from
    question q
    left join question_types qt on (1 = 1)
    left join question_exclusion qe on (q.question_id = qe.question_id and qt.question_type = qe.question_type and coalesce(qt.question_sub_type, '_') = coalesce(qe.question_sub_type, '_'))
where
    qe.question_id is null
order by 
    qt.question_type, qt.question_sub_type