Looks like you might need to add an additional field to your albums table. Then populate it with the artists id's that are associated with speccific albums. Also might want to add the same to your track table.
albumid int(10) unsigned NOT NULL auto_increment, artistid int (10), albumtitle varchar(255), year smallint(5) unsigned ----- Original Message ----- From: "Jim McAtee" <[EMAIL PROTECTED]> To: "CF-Talk" <[email protected]> Sent: Sunday, November 05, 2006 11:22 PM Subject: Query Problem > 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:259257 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
