CODEWITHSUNDEEP
mysql
Kir
Kir

Reputation: 694

Query using HAVING with multiple conditions in MYSQL

For example, there is a table with the properties of the goods. Each product can have multiple values ​​of the same properties.

Goods 

goods_id | property | value  

1        |color     | red  
1        |size      | 100  
1        |size      | 200  
2        |color     | red  
2        |color     | green  
2        |size      | 200

If I need to find all the items whith a color red AND a size of 100, then I run this query:

SELECT goods_id FROM goods
WHERE (property = 'color' AND value = 'red') OR
      (property = 'size' AND value = '100')
GROUP BY goods_id
HAVING COUNT(goods_id) = 2

MYSQL returns the goods_id = 1

But if I need to find all the items with a color red OR green AND a size of 100, the following query

SELECT goods_id FROM goods
WHERE (property = 'color' AND value IN ('red', 'green')) OR
      (property = 'size' AND value = '100')
GROUP BY goods_id
HAVING COUNT(goods_id) = 2

returns the ids 1 AND 2, but should only 1.
How can I change the second query to return the correct result? Thanks for any advice

Upvotes: 1

Views: 462

Answers (2)

Edper
Edper

Reputation: 9322

This is your Table:

goods_id | property | value
  1       |    color    | red
  1       |    size      | 100
  1       |    size      | 200
  2       |    color    | red
  2       |    color    | green
  2       |    size      | 200

And this is your first query:

  SELECT goods_id FROM goods
  WHERE (property = 'color' AND value = 'red') OR
  (property = 'size' AND value = '100')
  GROUP BY goods_id
  HAVING COUNT(goods_id) = 2

Without the HAVING clause first and only the WHERE statement you will have the result:

  1       |    color    | red
  1       |    size      | 100
  2       |    color    | red

But with the HAVING clause now since you want only goods_id having a count of 2 then what remains are:

  1       |    color    | red
  1       |    size      | 100

And since you group it only the 1 remains since you display the goods_id.

Now, on your second query you have:

 SELECT goods_id FROM goods
 WHERE (property = 'color' AND value IN ('red', 'green')) OR
  (property = 'size' AND value = '100')
 GROUP BY goods_id
 HAVING COUNT(goods_id) = 2

Based on the criteria again without the HAVING clause you want property of color and with value of either (that means OR) red or green OR property of size and value of 100. So, you will get the result of:

goods_id | property | value
  1       |    color    | red
  1       |    size      | 100
  2       |    color    | red
  2       |    color    | green

And with the HAVING clause of COUNT(goods_id)=2 naturally the two goods_id of 1 and 2 will result.

In other words the query result is right.

So, if you want only one result then you could probably use

LIMIT 1

Or probably your query is wrong and therefore you need to change a portion of it.

Upvotes: 0

user359040
user359040

Reputation:

Change your HAVING condition to:

HAVING COUNT(DISTINCT property) = 2

Upvotes: 1

Related Questions