Q:

How to get items with the same column value

I have table 
Products(color_keyword,color)

My goal is:
When I'm doing `
SELECT * 
  FROM Products

add to each product list of colors (it can be comma separated even) with the same color_keyword.
Is that possible somehow with database tools?
If not, maybe it's possible to reorganise database/tables to achieve this result.
I know that I can do it with foreaching each product returned in node.js and make for each product selection request with  WHERE color_keyword=current_product_color_keyword, but I'm looking for something more elegant solution.
Thanks.

A:

We could use a correlated subquery, but be careful with large sets. The correlated subquery will get executed for each row returned by the outer query, so we want to ensure that suitable index is available. In terms of performance, this approach has the potential to eat our lunch and our lunch box too.
 SELECT p.id
      , p.color_keyword
      , p.color
      , ( SELECT GROUP_CONCAT(DISTINCT q.color ORDER BY q.color)
            FROM products q
           WHERE q.color_keyword <=> p.color_keyword
        ) AS color_list 
   FROM products p 
 WHERE ...   

Another approach is to use a join to an inline view 
 SELECT p.id
      , p.color_keyword
      , p.color
      , q.color_list
   FROM products p 
   LEFT
   JOIN ( SELECT r.color_keyword 
               , GROUP_CONCAT(DISTINCT r.color ORDER BY r.color) AS color_list
           FROM products r
          GROUP BY r.color_keyword
        ) q
     ON q.color_keyword <=> p.color_keyword  
 WHERE ...   

