CODEWITHSUNDEEP
mysqlsql
Mehravish Temkar
Mehravish Temkar

Reputation: 4365

Get record based on combination of values of a table

I have two tables : option_combination & option_combination_mapping

option_combination

enter image description here

option_combination_mapping

enter image description here

I have an array of option_value_id and product_id, I want the record where the product_id and all the option_value_id of my array are mapped. If say my array is (11,7,9), there are mappings for same product for say (11, 7, 10) Using this query i get the mapping for (11,7,9) but I also get the mappings for 11,7 of (11,7,10)

SELECT * FROM oc_option_combination_mapping ocm LEFT JOIN oc_option_combination oc ON (ocm.option_combination_id = oc.option_combination_id) where oc.product_id = 4 AND ocm.option_value_id IN (11,7,9)

enter image description here I tried this query which is giving be desired result but for only one combination

SELECT * FROM oc_option_combination oc LEFT JOIN oc_option_combination_mapping ocm ON (oc.option_combination_id = ocm.option_combination_id) where oc.product_id = 4 AND ocm.option_value_id IN (11,7,9) HAVING COUNT(DISTINCT ocm.option_value_id = 3)

enter image description here when I try for another combination 11,7, 10 :

SELECT * FROM oc_option_combination oc LEFT JOIN oc_option_combination_mapping ocm ON (oc.option_combination_id = ocm.option_combination_id) where oc.product_id = 4 AND ocm.option_value_id IN (11,7,10) HAVING COUNT(DISTINCT ocm.option_value_id = 3)

enter image description here I get the same result again whereas it should fetch the record with option_combination_id 2 and not with option_combination_id 1

I need help to fetch price and quantity for various different unique combinations of option_value_id of a product with the query.

For example the result of query should be the record with option_combination_id 1 (price 10) for option_value_id (11,7,9) and product_id 4 and the result of query should be the record with option_combination_id 2 (price 20) for option_Value_id (11,7,10) and product_id 4

Upvotes: 1

Views: 231

Answers (1)

Shadow
Shadow

Reputation: 34305

The last one is almost correct, just you moved the =3 into the count(), but it should be outside of it. Also, you missed the group by clause:

SELECT oc.option_combination_id
FROM `option_combination` oc
INNER JOIN `option_combination_mapping` ocm ON (oc.`option_combination_id` = ocm.`option_combination_id`)
where oc.product_id = 4 AND ocm.`option_value_id` IN (11,7,10)
Group by oc.option_combination_id
HAVING COUNT(DISTINCT ocm.`option_value_id`)=3

I also changed changed the left join into an inner one since you filter on the right hand siďe table anyway.

Upvotes: 2

Related Questions