下面的查询将根据给定的供应商标识、类别标识、子类别标识和子类别值标识，为您提供具有产品标识和产品名称的不同产品。
让我知道这是否适合你！

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;

你想要什么 group by 以及 having . 有点不清楚你所说的“所有条件”是什么意思，但它看起来是这样的：

SELECT p.*
FROM products p JOIN
     product_category pc
     ON p.product_id = pc.product_id 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, pc.subcategory_value_id) IN ( (1, 1), (2, 4) )
GROUP BY p.product_id  -- this is okay because it is presumably the primary key
HAVING COUNT(DISTINCT pc.subcategory_id) = 2;

笔记：
这个 WHERE 子句将 LEFT JOIN 变成一个 INNER JOIN ，所以用右边的 JOIN .
我假设您需要子类别表中的值对。
这个 HAVING 子句坚持两个子类匹配。

似乎你不应该用左连接 product_category table。left join关键字返回左表中的所有记录( product_category )请尝试以下方式：

SELECT
                p.product_id,
                p.product_name,
                p.product_photo,
                p.size,
                p.price,
                p.status,
                p.product_image_path
            FROM
                products AS p
            INNER 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)