Posted by Rik Wasmus on 11/11/07 12:29

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 wa=
s 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 =3D
> mARTISTS.ArtistID) as Artist1,
> MnArtist1Desc,
> (select ArtistName from mARTISTS where tblMain.MnArtist2 =3D
> mARTISTS.ArtistID) as Artist2,
> MnArtist2Desc
> from tblMain
> where MnEvent =3D '22'
> ORDER BY MnOrder

And that's why you should post in a MySQL group. This query still does n=
ot =

require a subselect.

SELECT m.ID, a1.ArtistName, m.MnArtist1Desc, a2.ArtistName, =

m.MnArtist2Desc =

FROM tblMain m
LEFT JOIN mARTISTS a1
ON a1.ArtistId =3D m.MnArtist1
LEFT JOIN mARTISTS a2
ON a2.ArtistId =3D m.MnArtist2
WHERE m.MnEvent =3D '22';
ORDER BY m.MnOrder

.... allthough this seems limited to 2 Artists. Fine if the business logi=
c =

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

any number of artists from 0 to virtually inifinity.
-- =

Rik Wasmus

[Back to original message]