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

Sarah Adams sarahwbs at gmail.com
Wed Apr 15 14:58:51 CDT 2009


>> 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
> 
> sadly, the elegant subquery has a couple warts    ;o)
> 
> this part requires further explanation --
> 
>      WHERE o.OptionID IN (i.OptionID)
> 
> the only time i've seen people do this, it is because the column value 
> inside the IN list is actually a comma-delimited string of id numbers
> 
> is this the case here?
> 
> or is i.OptionID actually just a single number?
> 
> rudy

Sorry, I should have pointed out that UserCartItems.OptionID (aka
i.OptionID) is a varchar field, that holds a comma-delimited list of
option IDs.

It almost seems like the value is being passed as, e.g.
  '85, 143'
instead of
  85, 143
and therefor being cast to int and only returning the option info for
the first id in the list.

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



More information about the thelist mailing list