CODEWITHSUNDEEP
sqlsql-server
Toni
Toni

Reputation: 442

check if the column value exists in subquery

i have 3 tables Product Category and ProductCategory.

Product table:

ProductID ProductName
1             P1
2             P2
3             P3

Category table:

CategoryID CategoryName
1              C1
2              C2
3              C3

ProductCategory:

ProductID CategoryID
1            1
1            2
1            3
2            3
3            1
3            2

I need a query which returns products which fall under more than 1 categories. Based on the table data above the result would be:

ProductID     ProductName
    1             P1
    3             P3

So i wrote a query to fetch all the ProductID's which have more than one CategoryID's as below:

select ProductID,count(CategoryID)    
from ProductCategory   
group by Productid   
having count(CategoryID)>1)

But when i try to display product details using the below query i get an error:

select *
from Product
where ProductID in (
    select ProductID,count(CategoryID)  
    from ProductCategory 
    group by Productid 
    having count(CategoryID)>1))

Is my query wrong? How do i get the required product details which fall in more than one categories?

Upvotes: 2

Views: 12869

Answers (2)

jherax
jherax

Reputation: 5267

You can try use CROSS APPLY Operator in SQL Server

SELECT DISTINCT C.ProductID,C.ProductName,A.CategoryID,A.Total
FROM Product C
CROSS APPLY (
    Select CA.CategoryID,Total=COUNT(*)
    From ProductCategory CA
    Where C.ProductID=CA.ProductID
    Group By CA.CategoryID Having COUNT(*)>1
) AS A
ORDER BY A.Total DESC

Take a look: http://explainextended.com/2009/07/16/inner-join-vs-cross-apply/

Upvotes: 0

John Woo
John Woo

Reputation: 263723

Remove the COUNT() in the subquery. The result of the subquery when used on IN clause must have only one returned column.

SELECT  *
FROM    Product
WHERE   ProductID IN 
        (
            SELECT  ProductID
            FROM    ProductCategory
            GROUP   BY Productid
            HAVING  count(CategoryID) > 1
        )

or by using JOIN

SELECT  a.*
FROM    Product a
        INNER JOIN 
        (
            SELECT  ProductID
            FROM    ProductCategory
            GROUP   BY Productid
            HAVING  count(CategoryID) > 1
        ) b ON a.ProductID = b.ProductID

Upvotes: 6

Related Questions