Use select distinct on the original query to eliminate duplicate records. >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:259285 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
