mysql - 来自不同表的MySQL多个AND条件

Question

我有以下数据库结构：

产品

+---------+-------+--------+
|  name   |  upc  |  model |
+---------+-------+--------+
| Value 1 | 1,234 | phone  |
| Value 2 | 2,345 | tablet |
+---------+-------+--------+

产品导入

+-----------+---------------+----------+
| to_import | id_oc_prd_map | category |
+-----------+---------------+----------+
| 1         |       1,234   | apple    |
| 2         |       2,345   | banana   |
+-----------+---------------+----------+

我想从具有多个条件的产品表中获取行：

SELECT *
FROM product
WHERE upc IN (SELECT id_oc_prd_map FROM product_import) 
AND product.model = 'phone' 
AND product_import.category = 'apple'

• 通过公共键选择项目（upc & id_od_prd_map）
• 型号='电话'
• 类别='苹果'

在上面的例子中，我应该得到

+---------+-------+--------+
|  name   |  upc  |  model |
+---------+-------+--------+
| Value 1 | 1,234 | phone  |

作为结果，但第二个 AND 语句出现错误，因为它在另一个表中。

标签： mysqlsql

Answer 1

只需找到upcwhere category 等于“apple”：

SELECT *
FROM product
WHERE upc IN (SELECT id_oc_prd_map FROM product_import WHERE import.category = 'apple') 
AND product.model = 'phone' 

或使用：

SELECT *
FROM product
INNER JOIN product_import ON product_import.category = 'apple'
                          AND product_import.id_oc_prd_map = product.upc
WHERE product.model = 'phone'