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

[r937]

> UserCartItems.OptionID (aka i.OptionID) is a varchar field,
> that holds a comma-delimited list of option IDs.

next question: is this mysql, or some other database system?

i didn't read matt's link to tom kyte's blog, because although tom is highly 
respected, he pretty much only covers oracle (~ptui~)

if you're on mysql, you can use FIND_IN_SET

   SELECT 99999 AS OrderID
        , i.ProductID
        , i.Quantity
        , p.NameDE AS ArchiveName
        , p.Price AS ArchivePrice
        , q.ArchiveOptions
     FROM UserCartItems i
   INNER
     JOIN ( SELECT o.OptionID
                 , GROUP_CONCAT(
                      CONCAT(s.NameDE,': ',o.NameDE)
                          ) AS ArchiveOptions
              FROM OptionSets AS s
            INNER
              JOIN Options AS o
                ON o.SetID = s.SetID
            GROUP
                BY o.OptionID ) AS q
       ON FIND_IN_SET(q.OptionID,i.OptionID)
   INNER
     JOIN Products p
       ON p.ProductID = i.ProductID

if you're not on mysql, there are other wayzzzz.....

;o)