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?

Answer

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
        ) 

• SQLFiddle Demo

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

• SQLFiddle Demo