CODEWITHSUNDEEP
mysqlsqlbetweenzen-carthaving-clause
Ronald Joseph
Ronald Joseph

Reputation: 45

How to fetch ID from database if particular column has both value

Database columns, Now i want to get those Product IDs only those having OPTIONS_VALUES_ID (12 and 31)

Database Image 

  SELECT DISTINCT  pa.products_id,options_values_id,options_id , COUNT(options_values_id) FROM products_attributes pa WHERE 
            pa.products_id IN (3331,3452,3455) AND pa.options_values_id IN (12,31) 
            GROUP BY pa.products_id
            HAVING (pa.options_values_id) >  0

Above Query Result Above Query Result

I could not figure out how to get only those products id which having define options_value_id by user

Upvotes: 0

Views: 1179

Answers (2)

G one
G one

Reputation: 2729

I think this is what you meant...

SELECT products_id,COUNT(options_values_id) 
FROM products_attributes WHERE 
options_values_id in(12,31)
GROUP BY products_id
having COUNT(options_values_id)>=2

SQLFiddle

Upvotes: 2

Ashish Jagtap
Ashish Jagtap

Reputation: 2819

No need to use DISTINCT because GROUP BY will remove duplicate values of your pa.products_id

SELECT pa.products_id,options_values_id,options_id , COUNT(options_values_id) 
FROM products_attributes pa 
WHERE pa.options_values_id IN (12,31) 
GROUP BY pa.products_id

hope this will help you...!

Upvotes: 0

Related Questions