duke duke - 4 months ago 7
MySQL Question

Using multiple times AND in where condition not giving results

I have a table as shown in the below pic, its a stripped version of bigger table.

enter image description here

Question

I have to get the products in category 9030 and 1993, so in this case it should show 1 row. But when I execute below query it gives null as result.


SELECT * FROM catalog_category_product WHERE category_id='1993' AND
category_id=9022


So expected output would be 1 row with product_id 33839, is there any way to intersect the results.

I am not very much that familiar with MySQL queries, please advise.

Answer

In order to get intersected result:

Using NOT IN:

SELECT 
 C1.product_id
FROM catalog_category C1
WHERE C1.product_id IN 
(SELECT 
   C2.product_id 
 FROM catalog_category C2
 WHERE C2.category_id = 9030 
)
AND C1.category_id = 1993

Using INNER JOIN:

SELECT DISTINCT
    C1.product_id
FROM
    catalog_category C1
INNER JOIN catalog_category C2 ON C1.product_id = C2.product_id
WHERE   C1.category_id = 9030 
AND C2.category_id = 1993;

Using EXISTS:

SELECT 
 C1.product_id
FROM catalog_category C1
WHERE EXISTS (
  SELECT 1 
  FROM catalog_category C2 
  WHERE C2.category_id = 1993
  AND C1.product_id = C2.product_id
)
AND C1.category_id = 9030 
Comments