[thelist] an elegant correlated sub-query that doesn't want to
Sarah Adams
sarahwbs at gmail.com
Thu Apr 16 12:46:30 CDT 2009
> 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
More information about the thelist
mailing list