|
|
Posted by Jerry Stuckle on 11/12/07 01:59
Rik Wasmus wrote:
> 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,
As long as people keep answering his SQL questions he won't make any
attempt to locate a MySQL group. Not having it on his server is a
piss-poor excuse. There are too many other ways he can get to it.
And there are MySQL mailing lists he could use.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================
Navigation:
[Reply to this message]
|