[thelist] an elegant correlated sub-query that doesn't want to
Sarah Adams
sarahwbs at gmail.com
Thu Apr 16 14:24:44 CDT 2009
> 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.
I *think* Rudy was being facetious, since his column is (technically)
about Oracle :)
>> 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.
I was thinking this might return a full set of all set/option combinations:
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
I'm hoping the optimizer can figure it out.
--
sarah adams
my son: http://clarkeadams.com
twitter: http://twitter.com/sarahwbs
portfolio: http://sarah.designshift.com
More information about the thelist
mailing list