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

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

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

Shelly 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,

Please start asking these questions in comp.databases.mysql. That's
where they belong.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================