mysql - 如何在 SQL 的 WHERE 子句中使用 IN 运算符和 AND 运算符...？

Question

我在我的 CodeIgniter 模型中使用此数据库查询。

SELECT
                p.product_id,
                p.product_name,
                p.product_photo,
                p.size,
                p.price,
                p.status,
                p.product_image_path
            FROM
                products AS p
            LEFT JOIN
                product_category AS pc
            ON
                p.product_id = pc.product_id
            LEFT JOIN
                vendor_products AS vp
            ON
                vp.product_id = pc.product_id
            WHERE
                pc.category_id = 2
            AND
                vp.vendor_id = 36
            AND 
                pc.subcategory_id IN (1,2)
            AND
                pc.subcategory_value_id IN (1,4)

它返回了我:

我只想要那些满足 sub_category_value_id 所有条件的产品。现在它正在返回所有条件。

我是数据库新手，对查询不太了解。

Answer 1

下面的查询将根据给定的供应商 ID、类别 ID、子类别 ID 和子类别值 ID 为您提供具有 Product_ID 和 Product_name 的不同产品。

请告诉我这是否适合您!

SELECT 
DISTINCT p.product_id,
p.product_name 
FROM products AS p
LEFT JOIN product_category AS pc ON p.product_id = pc.product_id
LEFT JOIN vendor_products AS vp ON p.product_id = vp.product_id
LEFT JOIN subcategories AS sc ON sc.subcategory_id = pc.subcategory_id
LEFT JOIN subcategories_value AS scv ON scv.subcategory_value_id = pc.subcategory_value_id
WHERE vp.vendor_id = 2 
    AND vp.category_id = 2 
    AND pc.subcategory_id IN (1, 2) 
    AND scv.subcategory_value_id IN (1, 4)
ORDER BY p.product_id;