0

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

Ronald Joseph
  • 45
  • 1
  • 13
  • Can you clarify your question ? – Raptor Feb 10 '14 at 10:02
  • So what's **wrong** with your results? Are you expecting all rows for an ID if at least one row has that options_values_id? – Anthony Grist Feb 10 '14 at 10:03
  • I am not good in mysql, I want those id which having both options-values-id, Currently it get those which having options-values-id 31 or 12, i want the condition to get product having 31 and 12. U can check example on site which i am working on Price sorting http://junoon2.co.uk.dnpserver.com/index.php?main_page=index&cPath=21_23&show=&psort=&psort=&attval%5B%5D=12&attval%5B%5D=31 – Ronald Joseph Feb 10 '14 at 10:08
  • Can you please tell us your expected output.. – G one Feb 10 '14 at 10:10
  • In assuming your desired output is your selection of rows in red, I am not sure it can be done with a single SQL query. You would need to input the results of one query into another query – SyntaxLAMP Feb 10 '14 at 10:12
  • Currenty it get 3 rows but only ID 3452 has both option_values_id. so it is not working properly. I need to get only those Ids that having both option_values_id in a column – Ronald Joseph Feb 10 '14 at 10:18
  • In Front-end, if Green color is selected and Size is select. It should Show only Green color dresses only. Please Check the Link below: http://junoon2.co.uk.dnpserver.com/index.php?main_page=index&cPath=21_23&show=&psort=&psort=&attval%5B%5D=12&attval%5B%5D=31 – Ronald Joseph Feb 10 '14 at 10:26

2 Answers2

2

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

G one
  • 2,679
  • 2
  • 14
  • 18
  • Yes this I wanted. SELECT products_id,COUNT(options_values_id) FROM products_attributes WHERE options_values_id IN(12,31) AND products_id IN (3331,3452,3455) GROUP BY products_id HAVING COUNT(options_values_id)=2 – Ronald Joseph Feb 10 '14 at 10:48
0

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...!

Ashish Jagtap
  • 2,799
  • 3
  • 30
  • 45
  • I don't think this is what the OP wants..As this gives the same result as the query posted by OP.. – G one Feb 10 '14 at 10:28
  • Nope this is not What i want....! Thanks for help. I want get Product Id which having both option values in a column – Ronald Joseph Feb 10 '14 at 10:30
  • In Front-end, if Green color is selected and Size is select. It should Show only Green color dresses wHICH HAVING GIVEN SIZE. Please Check the Link below: http://junoon2.co.uk.dnpserver.com/index.php?main_page=index&cPath=21_23&show=&psort=&psort=&attval%5B%5D=12&attval%5B%5D=31 – Ronald Joseph Feb 10 '14 at 10:31
  • you mean that you want details of those items whose having both the pa.options_values_id ie. 12 and 32 right ? – Ashish Jagtap Feb 10 '14 at 10:36
  • 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) HAVING pa.products_id IN (3331,3452,3455) GROUP BY pa.products_id – Ronald Joseph Feb 10 '14 at 10:36