如何在另一个表中选择不同的行并进行交叉检查？

Question

I have 3 tables, users and tasks and completed_tasks. So basically I want to select all tasks where the user_id = 2 AND also check that the task does not exist in another table` the So here is my tables:

users table:

+----+-------+
| id | name  |
+----+-------+
|  1 | John  |
|  2 | Sally |
+----+-------+

tasks table:

+----+-----------+---------+
| id | task_name | user_id |
+----+-----------+---------+
|  1 | mop floor |       2 |
|  2 | dishes    |       1 |
|  3 | laundry   |       2 |
|  4 | cook      |       2 |
+----+-----------+---------+

completed_tasks table:

+----+---------+---------+
| id | task_id | user_id |
+----+---------+---------+
|  1 |       1 |       2 |
+----+---------+---------+

Here is my current SELECT code for my MySQL database:

$db = "SELECT DISTINCT tasks.task_name, users.name FROM tasks LEFT JOIN ON users.id = tasks.user_id WHERE tasks.user_id = 2";

THe problem I'm having is: I want it to search in completed_tasks table and if the task exists, then don't select that task.

I tried to do that by adding the following but it did not work:

LEFT JOIN completed_tasks ON completed_tasks.user_id = 2

That did not work because if I had multiple completed tasks, it would just ignore it all together.

I want the end result should return the user's name and task name of task 3 and 4.

Also, performance is critical in my application. I could use PHP and loop through the arrays and do SELECT for each of them but that would not be good for performance.

Answer 1

You have a few ways to do this.

You can use a LEFT JOIN and then check for NULL in the optional table.

SELECT a.name, b.task_name
FROM users a
JOIN tasks b ON a.id = b.user_id
LEFT JOIN completed_tasks c ON c.task_id = b.id AND c.user_id = b.user_id
WHERE c.id IS NULL
;

You can do a NOT EXISTS sub-query

SELECT a.name, b.task_name
FROM users a
JOIN tasks b ON a.id = b.user_id
WHERE NOT EXISTS (
    SELECT 1
    FROM completed_tasks c
    WHERE c.task_id = b.id AND c.user_id = b.user_id)
;