Select records with same foreign key but different column value in another table (MYSQL)-CodePudding

Assume I have two tables with structures as defined.

Table1:
| question_id | question_type |
| ----------- | ------------- |
|     1       |       1       |
|     1       |       2       |
Table2:
| question_id | question_type | completed |
| ----------- | ------------- | --------- |
|     1       |       1       |     1     |

Now I want to select the record which has no entry in table2, I have done this but it does not work if the question_id is same.

Select * from table1 t1 
left join table2 t2 on
(t1.question_id = t2.question_id and t1.question_type = 1) or
(t1.question_id = t2.question_id and t1.question_type = 2)
where t2.completed is null
and question_id = 1

The Problem is whenever there is a record for the same question_id with different type, no record is selected where as the result should give question_id = 1 with question_type = 2

CodePudding user response：

I would use exists logic here:

SELECT t1.*
FROM Table1 t1
WHERE NOT EXISTS (
    SELECT 1
    FROM Table2 t2
    WHERE t2.question_id = t1.question_id AND
          t2.question_type = t1.question_type
);

If you wanted to use a join approach, we can try using a left anti-join here:

SELECT t1.*
FROM Table1 t1
LEFT JOIN Table2 t2
    ON t2.question_id = t1.question_id AND
       t2.question_type = t1.question_type
WHERE t2.question_id IS NULL;