issue on database query to fetch data with subquery

Question

I'm having trouble with a SELECT query.

    SELECT P.product_id
          , ( SELECT Category_id 
              FROM   Category C 
              WHERE  C.product_id = P.product_id) AS cat
    FROM Products P

This is supposed to pull the product_id from PRODUCTS and the category_id from CATEGORY. If the product is in multiple categories, I would like Categories field to return a list of those category_ids. The problem is a subquery will only return a single value. Does anyone know how to rewrite this?

Thanks!

Answer 1

A Sub-Query in Select MUST return a Scalar value, else it will error out. You can join two tables to get multiple values. or you can force sub-query to only return One value by using TOP clause.

Try

SELECT P.product_id
      ,PC.Category_id AS Categories
FROM Products P
INNER JOIN Product_Category PC ON PC.product_id = P.product_id

OR

SELECT P.product_id
     , (SELECT TOP 1 Category_id 
        FROM Product_Category PC 
        WHERE PC.product_id = P.product_id
        ORDER BY PC.SomeColumn ) AS Categories
FROM Products P

Answer 2

Use GROUP_CONCAT for this:

 SELECT P.product_id,  GROUP_CONCAT(PC.category_id SEPARATOR ', ')
   FROM Product_Category PC ,
        Products P
  WHERE PC.product_id = P.product_id
  GROUP BY P.product_id;

Check out this Fiddle.

Answer 3

You have a few options, some of which include:

select p.PRODUCT_ID, CATEGORY_ID
FROM products p
inner join product_category pc on p.product_id = pc.product_id

this will return a row for each product_Id and category_id combination.

Another option (Sql server 2005+ only)

SELECT p.product_id, 
STUFF((
  SELECT ','+ convert(varchar(50), pc.category_id) 
  FROM product_category pc 
  WHERE pc.product_id = p.product_id 
  FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, '') 
FROM products p

this will create a comma delimited list of your category IDs for each product id. See: http://sqlfiddle.com/#!6/303e7d/1

Looks like an answer has already been provided for accomplishing the mysql version