[thelist] an elegant correlated sub-query that doesn't want to
r937
rudy at r937.com
Wed Apr 15 21:49:37 CDT 2009
> 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)
More information about the thelist
mailing list