mysql select two columns by pair of values

Question

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%');

And I cannot use OR here because it will return all products with and I need only intersection, — Firestarter1, Jul 03 '15 at 20:07

Gordon Linoff · Answer 1 · 2015-07-03T20:54:55.697

1

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.

edited Jul 03 '15 at 20:54

answered Jul 03 '15 at 20:38

Gordon Linoff

1,242,037
58
646
786

M Khalid Junaid · Answer 2 · 2015-07-03T20:15:45.220

0

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

edited Jul 03 '15 at 20:15

answered Jul 03 '15 at 20:10

M Khalid Junaid

63,861
10
90
118

Thought about it but theese are dynamic. – Firestarter1 Jul 03 '15 at 20:12
Second: And what if there are same text fields in attributes 15 and 12, like "yes" or "no", It can return wrong result. – Firestarter1 Jul 03 '15 at 20:17
@Firestarter1 see another query hope you will get the idea to make dynamic conditions for your dynamic attributes for attribute_ids put them in `in()` clasue for text add `ORs` in like part and in the `HAVING` part put the count of attributes you want to compare `3,4,5...` – M Khalid Junaid Jul 03 '15 at 20:18

Proxytype · Answer 3 · 2015-07-03T20:38:08.283

0

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%'

edited Jul 03 '15 at 20:38

answered Jul 03 '15 at 20:10

Proxytype

712
7
18

Wont UNION behave same as OR here? – Firestarter1 Jul 03 '15 at 20:22
ummm... maybe i didnt understand you, the union will make the first select and then the second select and combine them together what exactly you want to happen? – Proxytype Jul 03 '15 at 20:27
There is correct SQL using UNION ALL and then using duplicates only http://stackoverflow.com/questions/2621382/alternative-to-intersect-in-mysql – Firestarter1 Jul 03 '15 at 20:33
you also can do something like this 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%' – Proxytype Jul 03 '15 at 20:36

mysql select two columns by pair of values

3 Answers3