MYSQL SELECT with INNER JOIN with multiple WHERE/CONDITIONS from the same column-CodePudding

I need to perform a SELECT on the PRODUCTS table that only returns result if the conditions of the PRODUCTS_DETAILS table are true

follow the sql:

select p.* from products p
join products_details pd
on p.id = pd.id_product
WHERE pd.details = 'quadra mar'
AND pd.details = 'prédio novo'

however when I put two or more "AND" it does not return results

objective: search for all products that have the "where" details

CodePudding user response：

Try,

SELECT p.* FROM products p
INNER JOIN product_details pd ON p.id = pd.id_product AND pd.details IN ('quadra mar', 'prédio novo');

take a look in your WHERE clausule, I think an OR condition will fix your issue too.

... WHERE pd.details = 'quadra mar' OR pd.details = 'predio novo'

And if you want only products that contains all the conditions in where clause, you can try:

SELECT pd.id_product, pd.details FROM products p
INNER JOIN product_details pd ON 
  p.id = pd.id_product AND 
  pd.details IN ('quadra mar', 'prédio novo') 
GROUP BY pd.id_product, pd.details 
HAVING count(*) > 1;