CODEWITHSUNDEEP
mysql
Jasmine Joseph
Jasmine Joseph

Reputation: 287

MYSQL select based on matching two condition for same column

I have a query like this

SELECT  `product_id` 
FROM    `attribute_value` 
WHERE   `spec_group_id` = 2 
AND     `product_id` = 1 
AND     `attribute_value` IN('samsung','56')

for the following data:

attribute_value_id   attribute_id   attribute_value   product_id   spec_group_id
1                    1              samsung           1            2
2                    2              55                1            2
3                    5              door-in-door      1            2

I need to get the product id only if it attribute value matches both samsung and 55. how to write a select query?

In my query If i change the attribute value as 56 then too it will get the product id.

Upvotes: 1

Views: 937

Answers (3)

forpas
forpas

Reputation: 164224

You need aggregation and the condition in the HAVING clause:

SELECT product_id 
FROM attribute_value 
WHERE spec_group_id = 2 AND attribute_value IN ('samsung', '56')
GROUP BY product_id
HAVING COUNT(*) = 2

Upvotes: 1

user16425306
user16425306

Reputation:

Here's one approach...

DROP TABLE IF EXISTS my_eav;

CREATE TABLE my_eav 
(attribute_value_id INT AUTO_INCREMENT PRIMARY KEY
,attribute_id INT NOT NULL
,attribute_value VARCHAR(12) NOT NULL
,product_id INT NOT NULL
,UNIQUE KEY(product_id,attribute_id)
);

INSERT INTO my_eav VALUES
(1,1,'samsung',1),
(2,2,'55',1),
(3,5,'door-in-door',1);     

CREATE VIEW v_eav AS
SELECT product_id
     , MAX(CASE WHEN attribute_id = 1 THEN attribute_value END) make
     , MAX(CASE WHEN attribute_id = 2 THEN attribute_value END) size
  FROM my_eav
 GROUP 
    BY product_id;
    
SELECT * FROM v_eav WHERE make = 'samsung' AND size = '55';

+------------+---------+------+
| product_id | make    | size |
+------------+---------+------+
|          1 | samsung | 55   |
+------------+---------+------+

Perhaps it's obvious that the view isn't strictly necessary to achieve the same thing.

Upvotes: 0

wayneOS
wayneOS

Reputation: 1425

You can use a sub-query for the IN-operator to first select all product_id with samsung as attribute_value and then select the ones where attribute_value is 55. Here is an example:

SELECT  `product_id` 
FROM    `attribute_value` 
WHERE   `spec_group_id` = 2 
AND     `product_id` IN(SELECT `product_id` FROM `attribute_value` WHERE `attribute_value` = 'samsung') 
AND     `attribute_value` = 55

Upvotes: 0

Related Questions