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

[Matt Warden]

On Thu, Apr 16, 2009 at 1:46 PM, Sarah Adams <sarahwbs at gmail.com> wrote:
>> 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~)
...
> Yes, sorry, this is in MySQL, not Oracle ;)

The point was that it explained why you can't simply use a varchar
field with a list of values as a list of values in your SQL.

And, Rudy, before you go knocking Oracle... the only reason there is a
problem in the first place is that the data model has non-atomic
values. I am a bit surprised that MySQL has built-in functions to
support this.

> 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


Sarah, I'm not sure what part you are getting at. I don't see anything
here that would trigger a full table scan.

-- 
Matt Warden
Cincinnati, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.