CODEWITHSUNDEEP
mysqlintersect
Firestarter1
Firestarter1

Reputation: 11

mysql select two columns by pair of values

I need to get all products from product_attributes table where I have several pairs of attribute id and filter text value.

It is made harder because text is stored as array separated by ;.

       TABLE product_attribute

        Fields product_id, attribute_id, text

        SELECT  pa.product_id  FROM  product_attribute as pa  

WHERE   (
 (pa.attribute_id = '15' AND pa.text LIKE '%Male%' ) 
  AND 
(pa.attribute_id = '12'  AND  pa.text  LIKE  '%Cream%'  )
 )

Obviously this does not work, because attribute_id cant be 12 and 15 same time.

And I cannot use OR here because it will return all products (all male) + (all cream)

and I need only intersection (male cream)

Got answer here. Alternative to Intersect in MySQL

My variant:

SELECT  paz.product_id 
FROM  (
( SELECT product_id FROM oc_product_attribute WHERE attribute_id = '15' AND text LIKE '%male%' )   
UNION ALL
( SELECT product_id FROM oc_product_attribute WHERE attribute_id = '12'  AND text  LIKE  '%creme%' )  
)  paz GROUP BY paz.product_id  HAVING COUNT(*)=2

And Gordon Linoff variant^

SELECT pa.product_id 
FROM product_attribute pa 
GROUP BY pa.product_id
HAVING SUM(pa.attribute_id = '15' AND pa.text LIKE '%Male%') > 0 AND
       SUM(pa.attribute_id = '12' AND pa.text LIKE '%Cream%');

Upvotes: 1

Views: 1633

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270483

I often approach these using group by and having:

SELECT pa.product_id 
FROM product_attribute pa 
GROUP BY pa.product_id
HAVING SUM(pa.attribute_id = '15' AND pa.text LIKE '%Male%') > 0 AND
       SUM(pa.attribute_id = '12' AND pa.text LIKE '%Cream%') > 0;

Each condition in the HAVING clause verifies that there is at least one row meeting the particular condition.

Upvotes: 1

Proxytype
Proxytype

Reputation: 722

SELECT  pa.product_id  FROM  product_attribute as pa  
WHERE   
(pa.attribute_id = '15' AND pa.text LIKE '%Male%' ) 
union
SELECT  pa.product_id  FROM  product_attribute as pa  
WHERE   
(pa.attribute_id = '12' AND pa.text LIKE '%Male%' )

combine with union...

you also can do everything together and distinct the results

select a.* from 
(select * from product_attribute where pa.attribute_id = '15' or 
pa.attribute_id = '12')  as a 
where  a.text LIKE '%Male%' or a.text = '%Cream%'

Upvotes: 0

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

If you have only 2 attributes to compare then use a self join to product_attribute 

SELECT 
  pa.product_id 
FROM
  product_attribute AS pa 
  JOIN product_attribute AS pa1 USING (product_id) 
WHERE pa.attribute_id = '15' 
  AND pa.text LIKE '%Male%' 
  AND pa1.attribute_id = '12' 
  AND pa1.text LIKE '%Cream%'

Another way would be using COUNT/GROUP BY /HAVING

SELECT 
  pa.product_id 
FROM
  product_attribute AS pa 
WHERE pa.attribute_id IN ('15', '12') 
  AND (
    pa.text LIKE '%Male%' 
    OR pa.text LIKE '%Cream%'
  ) 
GROUP BY pa.product_id 
HAVING COUNT(DISTINCT pa.attribute_id) = 2

Upvotes: 0

Related Questions