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
