Two selectss -- Are they the same?

am 11.11.2007 02:47:08 von Shelly

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.

--
Shelly

Re: Two selectss -- Are they the same?

am 11.11.2007 04:38:06 von Shelly

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

Re: Two selectss -- Are they the same?

am 11.11.2007 04:38:06 von Shelly

Re: Two selectss -- Are they the same?

am 11.11.2007 04:38:06 von Shelly

Re: Two selectss -- Are they the same?

am 11.11.2007 04:50:19 von zeldorblat

On Nov 10, 10:38 pm, "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

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

Re: Two selectss -- Are they the same?

am 11.11.2007 04:50:19 von zeldorblat

Re: Two selectss -- Are they the same?

am 11.11.2007 04:55:04 von Shelly

ZeldorBlat wrote:
> On Nov 10, 10:38 pm, "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
>
> 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

Re: Two selectss -- Are they the same?

am 11.11.2007 04:55:04 von Shelly

Re: Two selectss -- Are they the same?

am 11.11.2007 15:00:32 von Jerry Stuckle

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
==================

Re: Two selectss -- Are they the same?

am 11.11.2007 15:00:32 von Jerry Stuckle

Re: Two selectss -- Are they the same?

am 11.11.2007 15:00:32 von Jerry Stuckle