|
|
Posted by Shelly on 11/11/07 03:55
ZeldorBlat wrote:
> On Nov 10, 10:38 pm, "Shelly" <sheldonlg.n...@asap-consult.com> wrote:
>> Shelly wrote:
>>> Here is a post that I put into comp.lang.php before finding this
>>> newsgroup. Sorry for the double posting.
>>
>>> I came across this code:
>>
>>> SELECT AnnoID, AnnoTitle,
>>> (SELECT EventTitle FROM mEVENTS WHERE tblANNOUNCE.AnnoLink =
>>> mEVENTS.EventID)
>>> as AnnoLink,
>>> date_format(AnnoDate,'%M %D %Y') as date
>>> FROM tblANNOUNCE
>>> ORDER BY AnnoDate DESC, AnnoLink, AnnoTitle
>>
>>> I am having trouble reading this. Is this the same as:
>>
>>> SELECT a.AnnoID, AnnoTitle, e.EventTitle as AnnoLink,
>>> date_format(AnnoDate,'%M %D %Y') as date
>>> FROM tblANNOUNCE AS a, mEVENTS AS e
>>> WHERE a.AnnoLink = e,EventID
>>> ORDER BY date DESC, AnnoLink, AnnoTitle
>>
>>> In other words, do they do exactly the same thing? I can't get the
>>> former to run on the current version of MySQL. Apparantly (I was
>>> told) it ran on a later version of MySQL.
>>
>> 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
>>
>> --
>> Shelly
>
> You don't need sub-selects to do this one, either.
>
> select m.ID,
> a1.ArtistName as Artist1,
> m.MnArtist1Desc,
> a2.ArtistName as Artist2,
> m.MnArtist2Desc,
> from tblMain m
> join mARTISTS a1
> on m.MnArtist1 = a1.ArtistID
> join mARTISTS a2
> on m.MnArtist2 = a2.ArtistID
> where m.MnEvent = '22'
> order by m.MnOrder
Of course. Thanks. Its been a long day, I'm very tired and so as I slap my
head with "I shudda known that", I'll go to bed.
--
Shelly
Navigation:
[Reply to this message]
|