Alliteration at its finest... This is slightly off-topic, so please reply offlist. It's a MySQL question that I'm hoping someone can help me with. I figure since a large portion of PHP programmers also use MySQL, this is a valid question to at least -post- here. If I'm completely off about this, please let me know. I HAVE read the relevant portions of the MySQL manual, FYI.
I discussed this a bit on the list a few weeks ago, it's about a project I'm working on to consolidate 75 playlists into a database sortable by song, album, and artist. 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 and I apolgize again for posting this question here if it upsets anyone. I'm just really frustrated right now and not wanting to join yet another mailing list for one question. Please reply offlist. Thanks in advance. Jason Soza -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
