[thelist] an elegant correlated sub-query that doesn't want to cooperate

[Sarah Adams]

I'm working on this query, which should be a thing of beauty, but
instead is just being a PITA. I'm hoping some SQL guru
*cough*Rudy*cough* will know just where I'm going wrong here. If I
remove the correlated sub-query part, it works fine. As it is, it is
only returning one set/option pair (ArchiveOptions) regardless. Almost
as if i.OptionID is being treated as a string, instead of a list of numbers.

INSERT INTO OrderItems (OrderID, ProductID, Quantity, ArchiveName,
            ArchivePrice, ArchiveOptions)
     SELECT 99999 as OrderID, i.ProductID, i.Quantity,
            p.NameDE as ArchiveName, p.Price as ArchivePrice,
            (SELECT GROUP_CONCAT(CONCAT(s.NameDE, ': ', o.NameDE))
             FROM OptionSets s
             INNER JOIN Options o
             ON o.SetID = s.SetID
             WHERE o.OptionID IN (i.OptionID)
            ) as ArchiveOptions
       FROM UserCartItems i
 INNER JOIN Products p
         ON i.ProductID = p.ProductID

Let me know if you need to see some schema info! Just trying to keep my
first post brief.

(Please note that I can't change the database design, it's been in use
on this site for years. Otherwise I would normalize it properly and
ArchiveOptions wouldn't be a single field.)

-- 
sarah adams
my son: http://clarkeadams.com
twitter: http://twitter.com/sarahwbs
portfolio: http://sarah.designshift.com