How to extract missing Questions withg SQL-CodePudding

Table 1:

id | question_descr
===================
1  | blabla
2  | lorem
3  | ipsum
4  | dante
5  | alighieri

Table 2

id | user_name | question_id
============================
1  | test_user | 2
2  | test_user | 4
3  | test_user | 5

there are some missing, like: 1,3

Result Expected:

user_name | question_id
============================
test_user | 1
test_user | 3

CodePudding user response：

We can use a calendar table left anti-join approach here:

SELECT t2.user_name, t1.id AS question_id
FROM Table1 t1
CROSS JOIN (SELECT DISTINCT user_name FROM Table2) t2
LEFT JOIN Table2 t
    ON t.question_id = t1.id AND
       t.user_name = t2.user_name
WHERE t.id IS NULL
ORDER BY t2.user_name, t1.id;