MySQL 排除与连接表匹配的记录(行)

Question

我想从我的查询结果中排除 Table1 ID 等于 Table2 的 table1_id 的记录，这听起来很简单，但在我原来的查询中我有多个左连接表，我不知道如何考虑新表.

假设表 1(又名产品)和表 2 看起来像这样(我不会发布其他表，因为我认为它们在这种情况下无关紧要):

table1(product)                            table2
-----------------                          ---------------------------
id | sale | price | tax | active | m_id    id | table1_id | feature_id
1  | 1    | 100   | 1   | 1      | 1       1  | 2         | 333
2  | 1    | 100   | 1   | 1      | 1       2  | 2         | 444
3  | 1    | 100   | 2   | 1      | 1       3  | 2         | 555
4  | 1    | 100   | 2   | 1      | 1       4  | 3         | 555
                                           5  | 3         | 333

如果我们查看上面的表格，我需要排除 table1.id=2 和 table1.id=3，因为它们在 table2 中。

我的原始查询如下所示:

SELECT 
p.`id`,
0 AS `id_a`,
p.`ref` AS `Reference`,  
gy.`name` AS `MF`, 
pl.`name` AS `Full Name`,
pq.`quantity` AS `QTY`,  
IF(p.`tax`=1, ROUND(p.`price`*1.27), ROUND(p.`price`*1.18)) AS `Price`,
IF(p.`sale`=1, ROUND(sp.`discount`), "0") AS `discount amount`, 
CONCAT("http://example.com/",pl.`sef`,".html") AS `URL`, 
IF(pi.`id` IS NOT NULL, CONCAT( 'http://example.com/image/', pi.`id_image`, '-default.jpg' ), 'http://example.com/image/noimage.jpg') AS `image`
FROM `product` p
LEFT JOIN `stock` pq ON (p.`id` = pq.`id`)
LEFT JOIN `product_lng` pl ON (p.`id` = pl.`id`)
LEFT JOIN `manufacturer` gy ON (p.`m_id` = gy.`m_id`)
LEFT JOIN `image` pi ON (p.`id` = pi.`id`)
LEFT JOIN `discount_price` sp ON (p.`id` = sp.`id`)
WHERE p.`active`=1 AND pl.`lang_id`=1 AND pq.`quantity` > 0 AND p.`price` > 0
GROUP BY p.`id`
ORDER BY gy.`name`, pl.`name`

如何将表 2 纳入上述查询？

Answer 1

在 where 子句中，您可以添加:

and p.id not in (select table1_id from table2)