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

Question

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

"OR" is not my goal, I only need it to meet the conditions 'QUADRA MAR' and 'PRÉDIO NOVO', if I use "OR", it will search even if ID 2 does not have 'PRÉDIO NOVO' — Carlos Eduardo
– Carlos Eduardo, Commented Jan 20, 2022 at 14:54
I guess it's not possible for pd.details to be 'quadra mar' and 'prédio novo' at the same time. — stersym
– stersym, Commented Jan 20, 2022 at 14:55
@stersym,is there any other alternative? I'm a beginner in MYSQL — Carlos Eduardo
– Carlos Eduardo, Commented Jan 20, 2022 at 14:56
Alternative to what. You are asking for something impossible. (Unless i'm misundestanding something). Is there any way that your Column Details would have the values 'quadra mar' AND 'prédio novo' at the same time in the same row? Show us an example of your table. — stersym
– stersym, Commented Jan 20, 2022 at 14:58

marc_s · Accepted Answer · 2022-01-27 21:05:12Z

1

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 clause, 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;

edited Jan 27, 2022 at 21:05

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Jan 20, 2022 at 15:01

Rodrigo Chaves

1264 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Carlos Eduardo Over a year ago

@rodrigão, me salvou, seu último código me serviu perfeitamente, segue: select p.* from products p join products_details pd on p.id = pd.id_product AND pd.details IN ('quadra mar','prédio novo','frente mar') GROUP BY p.id HAVING count(*) > 2;

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related