to make it easier for others to help you. you should have posted the create table statement, insert statement for a small set of data so that anyone of us could easier reproduce your problem.
btw, are you sure you need 3 links between those 3 tables. please correct me if i'm wrong. i think 2 links should be enough. ie. artist <-> album <-> tracks. Jason Soza wrote: > > I'm trying to compile 75 playlists of 25+ songs each into a database > sortable by date the show was produced, track, album, and artist. I got > advice from someone as to how to layout the table structure, and basically > it was said that I should have a basic structure of 6 tables, one for > artists, one for albums, and one for tracks. These tables should contain > whatever info I want, i.e. song title, albums, etc., and a unique id for > each. I should have 3 more tables, each linking the unique id's of each > other table together. > > So, I have 3 tables as was suggested, one for songs, one for albums, one for > artists. Each has two columns, one being whatever information I'm entering, > and the other a MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY column. > > I have 3 other tables containing two MEDIUMINT columns, these link each of > the 3 others. I have: > artists_tracks > tracks_albums > albums_artists > > Each is setup so that the records represent 'links' - for example, > artists_tracks has artist_id and track_id columns. Each record is made up of > an artist_id and a matching track_id. > > My problem is, when querying some records are lost. But only on a certain > query. For instance: > SELECT tracks.tracks, artists.artists > FROM tracks, artists, artists_tracks > WHERE tracks.id = artists_tracks.track_id AND artists.id = > artist_tracks.tracks_id > > This works. It returns 614 records, each track mated with it's relative > artist. This same query works using albums and the tracks_albums table, > returning 614 records as well. Matching up albums to artists returns 413 > records, which is the correct number. > > However, the following query returns 451 instead of the expected 614 and I > can find no distinguishable pattern as to the records dropped: > SELECT tracks.tracks, albums.albums, artists.artists > FROM tracks, albums, artists, tracks_albums, albums_artists, artists_tracks > WHERE tracks.id = tracks_albums.track_id AND albums.id = > tracks_albums.album_id > AND albums.id = albums_artists.album_id AND artists.id = > albums_artists.artist_id > AND artists.id = artists_tracks.artist_id AND tracks.id = > artists_tracks.track_id > > I've tried LEFT JOIN as well, and have tried numerous other suggestions out > of the manual, but everything turns up 451 records. I haven't been able to > track down WHY records are missing - it seems almost random. If I sort by > album, sometimes all songs from an album have been listed, sometimes only 3 > of 5 or 7 of 10 are missing. > > Any help would be greatly appreciated. Thanks in advance. > > Jason Soza > > Jason Soza > '92 Twin Classic Red Miatae > Juneau, Alaska > http://www.phrog-net.com/sparkles/ > http://www.miatapix.net <--Is YOUR Miata here? > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Sammy Lau mailto: [EMAIL PROTECTED] - Tell me what you want and I'll tell you how you can live without it. --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
