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

问题描述：

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.

我有3个表，用户 code>和 tasks code>和 completed_tasks 代码>。所以基本上我想选择 user_id = 2 code>的所有任务，并检查该任务是否存在于另一个表中。所以这里是我的表： p>

用户 code> strong>表格： p>

  + ---- + ------- + 
 |  id |  name | 
 + ---- + ------- + 
 |  1 | 约翰| 
 |  2 |  Sally | 
 + ---- + ------- + 
  code>  pre> 
 
    tasks  code>  strong>表 ： p> 
 
 
  + ---- + ----------- + --------- + \ N |  id |  task_name |  user_id | 
 + ---- + ----------- + --------- + 
 |  1 | 拖把地板|  2 | 
 |  2 | 菜肴|  1 | 
 |  3 | 洗衣店|  2 | 
 |  4 | 做饭 2 | 
 + ---- + ----------- + --------- + 
  code>  pre> 
 
    completed_tasks  code>  strong> table： p> 
 
 
  + ---- + --------- + -----  ---- + \ N |  id |  task_id |  user_id | 
 + ---- + --------- + --------- + 
 |  1 |  1 |  2 | 
 + ---- + --------- + --------- + 
  code>  pre> 
 
 这是我的当前 我的MySQL数据库的 SELECT  code>代码： p> 
 
 
  $ db =“SELECT DISTINCT tasks.task_name，users.name FROM tasks LEFT JOIN ON users.id =  tasks.user_id WHERE tasks.user_id = 2“; 
  code>  pre> 
 
 我遇到的问题是：我希望它在 completed_tasks  code>中搜索 表，如果任务存在，则不要选择该任务。 p> 
 
 
我试图通过添加以下内容来实现，但它不起作用： p> 
 
   LEFT JOIN completed_tasks ON completed_tasks.user_id = 2 
  code>  pre> 
 
 
这不起作用，因为如果我有多个已完成的任务，它会一起忽略它 。 p> 
 
 

我希望最终结果应该返回任务3和4的用户名和任务名。 p> 
 
 
此外，性能关键  strong>在我的申请中。 我可以使用PHP并遍历数组并为每个数组执行 SELECT  code>，但这对性能不利。 p> 
  div>

答

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

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

相关推荐