mysql - SQL 查询适用于一个选择，但有两个选择返回 0 行

Question

我写了一个sql语法。当我只使用一个过滤器时，一切正常，但使用多个 mysql 时返回 0 个结果。非常感谢您的帮助。

select 
p.products_id, p.manufacturers_id, p.products_price, pd.products_name
from
products_description pd, 
products_to_categories p2c,
products p left join  manufacturers  m on p.manufacturers_id = m.manufacturers_id   
where 
p.products_id in (SELECT p2pef.products_id 
                  FROM products_to_products_extra_fields p2pef
                  WHERE 
               // filter no 1
               (p2pef.products_extra_fields_id = '102' and p2pef.products_extra_fields_value = 'tak') 
               // filter no 2
               and (p2pef.products_extra_fields_id = '94' and p2pef.products_extra_fields_value = 'tak')
              // no xx
                   )
and p.products_status = '1'
and p.products_id = pd.products_id
and p.products_id = p2c.products_id
and pd.language_id = '4'
and p2c.categories_id = '26'

group by p.products_id

----编辑

我想我搬到了这里，但是这个语法只返回一条记录而不是更多:

select 
p.products_id, pd.products_name
from
products_description pd, 
products_to_categories p2c,
products p
where 
EXISTS (SELECT p2pef.products_id 
                  FROM products_to_products_extra_fields p2pef
                  WHERE p2pef.products_extra_fields_id = '78' 
                 and p2pef.products_extra_fields_value = 'tak' 
                   and p.products_id = p2pef.products_id )
and 
EXISTS (SELECT p2pef.products_id 
                  FROM products_to_products_extra_fields p2pef
                  WHERE p2pef.products_extra_fields_id = '94' 
                  and p2pef.products_extra_fields_value = 'tak'
                    and p.products_id = p2pef.products_id )          


and p.products_status = '1'
and p.products_id = pd.products_id
and p.products_id = p2c.products_id
and pd.language_id = '4'
and p2c.categories_id = '26'

group by p.products_id

Answer 1

这是相同的查询，我用 OR 替换了 AND，试一试

编辑:使用评论中的信息，然后使用和 IN，这比你走的路要容易得多

SELECT
p.products_id, p.manufacturers_id, p.products_price, pd.products_name
FROM
products_description pd, 
products_to_categories p2c,
products p 
LEFT JOIN manufacturers  m ON p.manufacturers_id = 
m.manufacturers_id   
WHERE
p.products_id IN (SELECT p2pef.products_id 
              FROM products_to_products_extra_fields p2pef
              WHERE 
           // filter no 1
           (
           p2pef.products_extra_fields_id IN ('94', '102') 
           AND p2pef.products_extra_fields_value = 'tak'
           ) 

               )
AND p.products_status = '1'
AND p.products_id = pd.products_id
AND p.products_id = p2c.products_id
AND pd.language_id = '4'
AND p2c.categories_id = '26'

GROUP BY p.products_id