Infira Infira - 6 months ago 9
SQL Question

mysql select product which is in both categoreis

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 wich is represented in both select categires (Car and Motorcicle)
the result should be

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

Answer

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)
Comments