Posted by ZeldorBlat on 11/11/07 03:50

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

• Next in forum: Re: Two selectss -- Are they the same?
• Prev in forum: Re: Two selectss -- Are they the same?
• Thread view: Re: Two selectss -- Are they the same?

[Reply to this message]