Tricky query, joining tables

[DrDoc]

So, imagine that I have two tables:

Items 
+------+--------+ 
¦ItemID¦ColorIDs¦ 
+------+--------+ 
¦`````1¦1,2`````¦ 
+------+--------+ 
¦`````2¦1```````¦ 
+------+--------+ 
¦`````3¦2,3`````¦ 
+------+--------+

Colors 
+-------+---------+ 
¦ColorID¦ColorName¦ 
+-------+---------+ 
¦``````1¦White````¦ 
+-------+---------+ 
¦``````2¦Burgundy`¦ 
+-------+---------+ 
¦``````3¦SteelBlue¦ 
+-------+---------+

How can I write a query which will give me a result like this:

+------+------------------+ 
¦ItemID¦Colors````````````¦ 
+------+------------------+ 
¦`````1¦White,Burgundy````¦ 
+------+------------------+ 
¦`````2¦White`````````````¦ 
+------+------------------+ 
¦`````3¦Burgundy,SteelBlue¦ 
+------+------------------+

... in other words, where I get the a comma separated list of color names back in place of the comma separated list of color IDs?

So far I have tried:

SELECT IF( LENGTH( @itemid ) = LENGTH( @colors ), 
    ( 
      @itemid := NULL 
      AND @colors := NULL 
    ), 
    'set' 
  ) as Init, 
  IF( CAST(@itemid AS CHAR) = CAST(i.ItemID AS CHAR), 
    @colors := CONCAT( @colors, ',', c.ColorName ), 
    @colors := c.ColorName 
  ) as Setting, 
  @itemid := CAST(i.ItemID AS CHAR), 
  i.ItemID, 
  @colors as Colors 
FROM Items i 
  INNER JOIN Colors c ON FIND_IN_SET(c.ColorID, i.ColorIDs) 
WHERE 1 
ORDER BY i.ItemID ASC

... but it doesn't seem to work. :(

[DrDoc]

Maybe I should add that what I currently get is this:

+------+---------+ 
¦ItemID¦Colors```¦ 
+------+---------+ 
¦`````1¦White````¦ 
+------+---------+ 
¦`````1¦Burgundy`¦ 
+------+---------+ 
¦`````2¦White````¦ 
+------+---------+ 
¦`````3¦Burgundy`¦ 
+------+---------+ 
¦`````3¦SteelBlue¦ 
+------+---------+

When I expect:

+------+------------------+ 
¦ItemID¦Colors````````````¦ 
+------+------------------+ 
¦`````1¦White`````````````¦ 
+------+------------------+ 
¦`````1¦White,Burgundy````¦ 
+------+------------------+ 
¦`````2¦White`````````````¦ 
+------+------------------+ 
¦`````3¦Burgundy``````````¦ 
+------+------------------+ 
¦`````3¦Burgundy,SteelBlue¦ 
+------+------------------+

[DrDoc]

Hmm ... Nesting three queries I can get it to work:

SELECT ItemID, 
  ColorList 
FROM ( 
    SELECT IF( LENGTH( @itemid ) = LENGTH ( @colors ) 
      AND CONCAT_WS(',', LENGTH( @itemid ), LENGTH ( @colors ) ) IS NULL, 
        ( 
          @itemid := NULL 
          AND @colors := NULL 
        ), 
        'set' 
      ) as Init, 
      IF( CAST(@itemid AS CHAR) = CAST(ItemID AS CHAR), 
        @colors := CONCAT( @colors, ',', ColorName ), 
        @colors := ColorName 
      ) as Setting, 
      @itemid := CAST(ItemID AS CHAR), 
      ItemID, 
      @colors as ColorList 
    FROM ( 
        SELECT i.ItemID, 
          i.ColorIDs, 
          c.Name 
        FROM Items i 
          INNER JOIN Colors c ON FIND_IN_SET(c.ColorID, i.ColorIDs) 
        ORDER BY i.ItemID 
      ) combined 
    WHERE 1 
    ORDER BY ItemID, 
      LENGTH ( @colors ) DESC 
  ) final 
WHERE 1 
GROUP BY ItemID

But, then of course it's incompatible with even some fairly recent versions of MySQL (since subselects were a recent addition).

[coopster]

Yeah, that certainly isn't the ideal setup for this type of operation is it. I can see the issue right off. It is so very difficult to JOIN, GROUP, etc. when the column contains data that lends itself better to rows. Here nor there, when the data is in an *array* type format (i.e. the '1,2,3') I think I would go with your first query which returned the rows as you showed in message two. I would, however, simplify that query using MySQL's FIND_IN_SET operation. No need for the user-defined variables. Plus, if there happens to me more than one comma in the *array* type column, this automatically conforms without query modification.

SELECT 
 ItemID, 
 ColorName AS Colors  
 FROM Items  
 INNER JOIN Colors ON FIND_IN_SET(ColorID,ColorIDs) 
; 
-- 
-- Returns 
--  
+--------+-----------+ 
¦ ItemID ¦ Colors    ¦ 
+--------+-----------+ 
¦   1    ¦ White     ¦ 
¦   2    ¦ White     ¦ 
¦   1    ¦ Burgundy  ¦ 
¦   3    ¦ Burgundy  ¦ 
¦   3    ¦ SteelBlue ¦ 
+--------+-----------+

Now, PHP will make it real easy to get that result set into a format that you can use:

$sql = ' 
SELECT 
 ItemID, 
 ColorName AS Colors  
 FROM Items  
 INNER JOIN Colors ON FIND_IN_SET(ColorID,ColorIDs) 
'; 
$tmp = array(); 
$rows = mysql_query($sql); 
while ($row = mysql_fetch_array($rows)) { 
 if (isset($tmp[$row['ItemID']])) { 
  $tmp[$row['ItemID']] .= ',' . $row['Colors']; 
 } else { 
  $tmp[$row['ItemID']] = $row['Colors']; 
 } 
} 
print_r($tmp); 
exit; 
-- 
-- Returns 
--  
Array 
( 
  [1] => White,Burgundy 
  [2] => White 
  [3] => Burgundy,SteelBlue 
)

... just like you want.