CODEWITHSUNDEEP
mysqlsqlselectgroup-byin-operator
IseNgaRt
IseNgaRt

Reputation: 609

Select a product where it belongs in 2 different categories

This is the table virtuemart_product_categories:

enter image description here

What i want to do is select the virtuemart_product_id where the product is in category:307 and category:383. Something like that: (I know that the "AND" expression is completly wrong typed it as an example of what im trying to achieve). 

SELECT * 
FROM uhhu_virtuemart_products_en_gb AS a 
INNER JOIN uhhu_virtuemart_product_categories AS b ON a.virtuemart_product_id=b.virtuemart_product_id 
WHERE b.virtuemart_category_id=307 AND b.virtuemart_category_id=383

Is there a sql way to achieve that?maybe i need to create a new table and somehow make a relationship?I thought a really lame way using some php and seperate the queries but even if it work it will be really complex and bad way of coding. Thanks in advance.

Edit: Adding the products_en_gb table also:

enter image description here

Upvotes: 1

Views: 1232

Answers (2)

Paul Dixon
Paul Dixon

Reputation: 300855

If you want a product in two categories, then you could join uhhu_virtuemart_products_en_gb against itselt so that you have two category columns. Here is a simple example:

select p1.product_id from
product_category p1
inner join product_category p2 using(product_id)
where p1.category_id=307
and p2.category_id=383

One way to approach problems like this is to ask yourself "what must appear in row of the result enable me to filter the result?"

This isn't the only way to do it, but I think it's a fairly simple one to visualize.

Upvotes: 1

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT * 
FROM uhhu_virtuemart_products_en_gb AS a 
WHERE a.virtuemart_category_id IN (307, 383)
GROUP BY a.virtuemart_product_id 
HAVING COUNT(1) = 2

Upvotes: 2

Related Questions