如何正确匹配左连接并编写关联删除的SQL WHERE子句？

Alright, let's tackle this delete query problem. You need to remove records from product_d that are linked to the filter_id values 7 and 8 in d_category, and your existing query is missing the necessary joins and filter conditions. Here's how to fix it:

First, we need to add a join to d_category since that's where our target filter IDs live, then craft the WHERE clause to target those IDs and ensure we only delete relevant records. Here's the complete working query:

DELETE product_d 
FROM product_d 
LEFT JOIN d_name ON product_d.d_name_id = d_name.id
LEFT JOIN d_category ON d_name.filter_id = d_category.filter_id
WHERE d_category.filter_id IN (7, 8) 
  AND d_name.id IS NOT NULL;

Let's break down the key parts of the WHERE clause and the adjusted query:

• d_category.filter_id IN (7, 8): This directly targets the specific filter IDs you mentioned in d_category (7 and 8), ensuring we only operate on records related to those filters.
• d_name.id IS NOT NULL: Since we're using a LEFT JOIN, this filters out any product_d rows that don't have a matching entry in d_name—we only want to delete records that are actually linked to the specified filters, not unrelated ones.

If you also need to delete the corresponding d_name records along with product_d (since you mentioned deleting "filter information and data"), you can update the DELETE clause to include both tables:

DELETE product_d, d_name 
FROM product_d 
LEFT JOIN d_name ON product_d.d_name_id = d_name.id
LEFT JOIN d_category ON d_name.filter_id = d_category.filter_id
WHERE d_category.filter_id IN (7, 8);

Note: Multi-table deletes like this are supported in databases like MySQL, so make sure your DBMS allows this syntax.

内容的提问来源于stack exchange，提问作者Ivan