I know [Jerry and co.] that this is off-topic, but the sql group that I
could find online doesn't have much traffic, so here goes.

I cam 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

On Nov 10, 8:29 pm, "Shelly" <sheldonlg.n...@asap-consult.com> wrote:
> I know [Jerry and co.] that this is off-topic, but the sql group that I
> could find online doesn't have much traffic, so here goes.
>
> I cam 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.
>

They should be the same. I generally see the first type of query when
someone doesn't really understand joins. It also typically results in
horrible performance.

What do you mean when you say you "can't get the former to run?" Do
you get an error?

ZeldorBlat wrote:
> On Nov 10, 8:29 pm, "Shelly" <sheldonlg.n...@asap-consult.com> wrote:
>> I know [Jerry and co.] that this is off-topic, but the sql group
>> that I could find online doesn't have much traffic, so here goes.
>>
>> I cam 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.
>>
>
> They should be the same. I generally see the first type of query when
> someone doesn't really understand joins. It also typically results in
> horrible performance.

Hmmm. I learned the second type about 25 years ago and is always how I do
those joins. My client's customer changed servers and the new server has an
older version of MySQL. They ran into problems with many queries. I
started looking at the code, and the first one I came accross was the first
version (longer in length, I stripped it down for the posting). I copied
that code and went to phpmyadmin and did a sql query. It barfed and
complained mightily on the sub-select. I tried to analyze what the original
coder was intending to do, and came up with the second one (I wrote that
one). That one worked. I just wanted some verification from other
professionals that I was reading the original correctly in what the coder
was intending to do -- before I proceeded on my merry way modifying code in
some twenty modules.

--
Shelly

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

Greetings, Shelly.
In reply to Your message dated Sunday, November 11, 2007, 04:29:29,

> I know [Jerry and co.] that this is off-topic, but the sql group that I
> could find online doesn't have much traffic, so here goes.

> I cam 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.


Try this (if I understand it correctly)

SELECT
a.AnnoID, a.AnnoTitle, e.EventTitle as AnnoLink, date_format(a.AnnoDate,'%M %D %Y') as date
FROM tblANNOUNCE a LEFT (or INNER) JOIN mEVENTS e ON a.AnnoLink = e.EventID
ORDER BY AnnoDate DESC, AnnoLink, AnnoTitle

Choose LEFT or INNER depends on database structure.


--
Sincerely Yours, AnrDaemon <anrdaemon@freemail.ru>

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. This query still does not
require a subselect.

SELECT m.ID, a1.ArtistName, m.MnArtist1Desc, a2.ArtistName,
m.MnArtist2Desc
FROM tblMain m
LEFT JOIN mARTISTS a1
ON a1.ArtistId = m.MnArtist1
LEFT JOIN mARTISTS a2
ON a2.ArtistId = m.MnArtist2
WHERE m.MnEvent = '22';
ORDER BY m.MnOrder

.... allthough this seems limited to 2 Artists. Fine if the business logic
requires there are always 2, if not, the relation should be moved to a
relational tabel containing an tblMain ID and an ArtistId, so you can have
any number of artists from 0 to virtually inifinity.
--
Rik Wasmus

Shelly wrote:
> I know [Jerry and co.] that this is off-topic, but the sql group that I
> could find online doesn't have much traffic, so here goes.
>
> I cam 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.
>

Yes, it's off topic. And your inability to find comp.databases.mysql
does not excuse you posting it here.

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

Jerry Stuckle wrote:
> Shelly wrote:
>> I know [Jerry and co.] that this is off-topic, but the sql group
>> that I could find online doesn't have much traffic, so here goes.
>>
>> I cam 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.
>>
>
> Yes, it's off topic. And your inability to find comp.databases.mysql
> does not excuse you posting it here.

I do not have access to comp.database.mysql on my news server and, frankly
Jerry, I couldn't care less about your opinion here.

--
Shelly