Ok, this is by adding a albumid to you artisttrack table. Of course you will have multiple id's listed in there, but it is just a list of id's. My naming convention are a little different but you get the picture.
SELECT ar.artist_id, ar.name, a.album_id, a.album_name, a.year FROM artists ar LEFT OUTER JOIN artists_albums_tracks aat ON ar.artist_id = aat.artist_id LEFT OUTER JOIN albums a ON aat.album_id = a.album_id ----- Original Message ----- From: "Bobby Hartsfield" <[EMAIL PROTECTED]> To: "CF-Talk" <[email protected]> Sent: Monday, November 06, 2006 6:06 AM Subject: RE: Query Problem > Adding the artistid to the album table would cause some SERIOUS redundant > data... take Peeping Tom... there are at least 20 different artists on that > album. Adding artistid to the album table would mean you needed to add the > same album 20 times with a different artistid each time. > > I wouldn't add anything you didn't have to. You can do it with what you have > I'm sure. I'll just need to set it up and test it to give you actual syntax. > > Everything appears to be related to everything else one way or another so it > should be possible to do with one query using what you have. > > it's early but try this... > > seletc album.*, artist.* > from album inner join > ( > (artist inner join artisttrack ON artist.artistid = artisttrack.artistid) > Inner join track ON artisttrack.trackid = track.trackid > ) > on album.albumid = track.albumid > where albumid = #TheAlbumIdYouWantToGrabArtistsFor# > > Let me know what you get from that. If it doesn't work ill set up a database > and give it a better shot :-) > > > I would consider renaming 'year' and 'name' if possible. They are both > reserved words that may cause problems later. > > > > > -----Original Message----- > From: Doug Brown [mailto:[EMAIL PROTECTED] > Sent: Monday, November 06, 2006 7:37 AM > To: CF-Talk > Subject: Re: Query Problem > > 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:259317 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
