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

[Sarah Adams]

> 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
> 
> <snip>

Yes, sorry, this is in MySQL, not Oracle ;)

Rudy, you truly are the man. I had to move the GROUP_CONCAT out to the
main query, but that did it!

   SELECT 99999 AS OrderID
        , i.ProductID
        , i.Quantity
        , p.NameDE AS ArchiveName
        , p.Price AS ArchivePrice
        , GROUP_CONCAT(q.SetOption SEPARATOR '; ') AS ArchiveOptions
     FROM UserCartItems i
   INNER
     JOIN ( SELECT o.OptionID
                 , CONCAT(s.NameDE,': ',o.NameDE) AS SetOption
              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
   GROUP
       BY OrderID
        , i.ProductID
        , i.Quantity
        , p.NameDE
        , p.Price

Now, do you suppose that MySQL will be smart enough not to do full-table
scans for this bit of the query? I'll still use it either way, I'm just
curious.

   INNER
     JOIN ( SELECT o.OptionID
                 , CONCAT(s.NameDE,': ',o.NameDE) AS SetOption
              FROM OptionSets AS s
            INNER
              JOIN Options AS o
                ON o.SetID = s.SetID
            GROUP
                BY o.OptionID ) AS q

Woohoo! I love it when a good query comes together.

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