|
|
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
Navigation:
[Reply to this message]
|