I have a database of music CDs, something like:
album
-------------
albumid int(10) unsigned NOT NULL auto_increment,
albumtitle varchar(255),
year smallint(5) unsigned
track
-------------
trackid int(10) unsigned NOT NULL auto_increment,
tracktitle varchar(255),
tracknumber smallint(5) unsigned,
albumid int(10) unsigned
artist
-------------
artistid int(10) unsigned NOT NULL auto_increment,
name varchar(255)
artisttrack
-------------
artistid int(10) unsigned
trackid int(10) unsigned
There can multiple artists per track (e.g. the album "Back to Back" by
Duke Ellington and Johnny Hodges). For now, forget about albums that
might have different artist lineups per track. I'd like a listing that
shows _all_ of the artists on an album. Can this be done in a single
query, and in SQL (MySQL) only, without any manipulation by CF after the
query?
Here's what I have, but it can return only a single artist for the album:
SELECT ar.name,
a.albumtitle,
a.year
FROM album a
LEFT JOIN track t ON t.album = a.albumid
LEFT JOIN artisttrack at ON at.trackid = t.trackid
LEFT JOIN artist ar ON ar.artistid = at.artistid
GROUP BY a.albumid
ORDER BY a.albumtitle;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Introducing the Fusion Authority Quarterly Update. 80 pages of hard-hitting,
up-to-date ColdFusion information by your peers, delivered to your door four
times a year.
http://www.fusionauthority.com/quarterly
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:259244
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
