|
Posted by Steve on 11/12/07 02:29
"Shelly" <sheldonlg.news@asap-consult.com> wrote in message
news:13jf8ug9p75fo8a@corp.supernews.com...
> Rik Wasmus wrote:
>> On Sun, 11 Nov 2007 04:37:02 +0100, Shelly
>> <sheldonlg.news@asap-consult.com> wrote:
>>
>>> What the original coder was do was subselects. This is supported on
>>> MySQL
>>> 4.1 and above. The version on the server that it is being moved to
>>> is 4.0.27, so that is why it didn't work. For the one I posted, there
>>> was a simple way of recoding it. As I progressed futher into it,
>>> there were more
>>> complex cases that required a subselect. Otherwise, it required
>>> multiple,
>>> separate queries. For example:
>>>
>>> select
>>> ID,
>>> (select ArtistName from mARTISTS where tblMain.MnArtist1 =
>>> mARTISTS.ArtistID) as Artist1,
>>> MnArtist1Desc,
>>> (select ArtistName from mARTISTS where tblMain.MnArtist2 =
>>> mARTISTS.ArtistID) as Artist2,
>>> MnArtist2Desc
>>> from tblMain
>>> where MnEvent = '22'
>>> ORDER BY MnOrder
>>
>> And that's why you should post in a MySQL group.
>
> I don't have access to one on my news server. That is why I started the
> title with OT so that people like Jerry could simply not read it. I
> usually keep my posts here on topic and only php (you know that).
> However, there comes a time for an occasional exception when seeking help
> from fellow posters. The rules are there for all of us to live by, but
> who among us has not exceeded the speed limit by at least 5 mph on at
> least one occasion. IOW, the "rules" are not cast in stone. We can bend
> the limits a little on occasion -- especially when we warn the slower
> drivers that we intend to exceed the speed limit a little.
>
> As to your comment about the number of artists, I agree.. That is how I
> would have done it if I had designed the database. However, this is a
> database that the customer has, and it worked, so I am stuck with it.
shelly, as blat originally first stated, the two are essentially the same
query. however, most db's will perform a completely different execution plan
when the sub-selects are part of the main select. much better performance is
had when proper joining is done, as you showed in your second post. plus, it
is far easier to read because you know where the relationships (joins) are
made - and made *once*...as apposed to each sub-select call as seen above as
Artist1, Artist2.
proper joining will reduce the number of table scans and reduce the number
of opportunities to mess up a column if you need to change anything related
to this specific query...say, between the def. of Artist1 and Artist2. there
are other great reasonses, but these are two clearly good ones.
cheers.
Navigation:
[Reply to this message]
|