[thelist] an elegant correlated sub-query that doesn't want to
Matt Warden
mwarden at gmail.com
Thu Apr 16 13:04:08 CDT 2009
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.
More information about the thelist
mailing list