CODEWITHSUNDEEP
mysqlselect
Infira
Infira

Reputation: 167

MySQL select product which is in both categories

products

PRODUCTID;NAME
1        ;BMW
2        ;AUDI
3        ;Mercedes
4        ;Volvo

productCategories

CATID;CATNAME
1    ;Car
2    ;Motorcicle;
3    ;Bus

productCategiresRel

PRODUCTID;CATID;
1        ;1
1        ;2
2        ;1
3        ;1
4        ;3

if I use SQL

SELECT productID FROM  productCategiresRel WHERE catID IN (1,2)

the result is

productCategiresRel
PRODUCTID;CATID;
1        ;1
1        ;2
2        ;1
3        ;1
4        ;3

but I want the to select these products which is represented in both select categories (Car and Motorcicle).

The result should be:

PRODUCTID;CATID;
1        ;1 (Car)
1        ;2 (Motorcicle)

Upvotes: 0

Views: 172

Answers (2)

mdoyle
mdoyle

Reputation: 727

Or if you want to find all products that are in multiple categories--not just in Categories 1 and 2-- try this:

SELECT *
FROM products
WHERE productID IN (
    SELECT productID
    FROM productCategiresRel
    GROUP BY productID
    HAVING COUNT(*) > 1
);

Upvotes: 0

GavinCattell
GavinCattell

Reputation: 3963

You can use an EXISTS to check multiple times.

SELECT *
FROM products
WHERE EXISTS (SELECT '' 
    FROM productCategiresRel 
    WHERE catID = 1
    AND productCategiresRel.productId = products.productId)
AND EXISTS (SELECT '' 
    FROM productCategiresRel 
    WHERE catID = 2
    AND productCategiresRel.productId = products.productId)

Upvotes: 2

Related Questions