Never mind... Spotted this after I sent the post! This: SELECT dates.date, tracks.track, albums.album, artists.artist FROM dates, tracks, albums, artists, albums_tracks, tracks_artists, ex_playlist_tb WHERE example_playlist.id = dates.id AND example_playlist.track_id = tracks.id AND tracks.id = albums_tracks.track_id AND albums_tracks.track_id = tracks_artists.artist_id AND albums.id = albums_tracks.album_id AND artists.id = tracks_artists.artist_id ORDER BY date
Should have 'AND albums_tracks.track_id = tracks_artists.track_id' as the third AND instead of what's there now. Ooops! It works now, though! Yay! This is pretty cool. I'll have to let you know when I finish all of this - I'll be using PHP to display all this info via HTML. That'll be another fun project... Thanks again for your help! Jason -----Original Message----- From: Jason Soza [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 23, 2002 10:31 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Query Problem Okay, I think I'm -almost- there: I have these tables: tracks albums artists Each with an id MEDIUMINT NOT NULL PRIMARY KEY and a track, album, or artist column. Then I have 2 linking tables, albums<=>tracks, tracks<=>artists, these are: albums_tracks tracks_artists Each of these has all MEDIUMINT NOT NULL columns for album_id, track_id, artist_id, and an id MEDIUMINT NOT NULL PRIMARY KEY in the center. So albums_tracks has: album_id id track_id I created a dates table, this contains 2 columns, id MEDIUMINT NOT NULL PRIMARY KEY, and dates DATE. This has every date I've ever played a show in it, with a unique id for each date. Then I created a 'sample' playlist file, just trying to figure out how to do it. So I made it with 2 columns, id and track_id, both MEDIUMINT NOT NULL PRIMARY KEY. I figured the first id column could be matched with date.id to get the date of the show, and the track_id could be matched back to tracks.id and so on to get the rest of the data. So the data looks something like: id track_id 1 45 1 275 1 24 2 35 2 611 2 326 ... The first stab at a query to create a playlist ended up with 27,000 returns. That ain't right! So I used this query: SELECT dates.date, tracks.track, albums.album, artists.artist FROM dates, tracks, albums, artists, albums_tracks, tracks_artists, ex_playlist_tb WHERE example_playlist.id = dates.id AND example_playlist.track_id = tracks.id AND tracks.id = albums_tracks.track_id AND albums_tracks.track_id = tracks_artists.artist_id AND albums.id = albums_tracks.album_id AND artists.id = tracks_artists.artist_id ORDER BY date And got 90 returns, exactly double the amount of records in the dates table. The dates match the first track, but the first track repeats for all occurences of the date. The album is correctly matched with all occurrences of the track, but the artist is not correct. Is there something simple in my query I'm missing? Thanks for your help on this. Jason -----Original Message----- From: Opus [mailto:[EMAIL PROTECTED]] Sent: Thursday, May 23, 2002 9:34 PM To: Jason Soza; [EMAIL PROTECTED] Subject: RE: Query Problem Other than it is uglier than a Hut? The higest ideals of a database is to separate data into tables that keep track of data for the same objects. Some databases break these rules (oh and how) for simpler queries, and sometimes for faster queries. It is actually better to break the relationship info into separate tables. Makes it easier to track at some point. Something to add to the Albums_Tracks table would be index. IE Album_ID, Index, Track_ID 1, 1, 1 1, 2, 5 1, 3, 4 With this, you can keep trock of which order the tracks appear on albums. As for the Playlists. This is simular to album data. IE, an ordered collection of tracks with a title, and a date. So, expanding Albums (possible rename to Collections) we have: Collection_ID, Title, Date, Play_List 1, Oxygene, 1976 (use any date here, the year is normally all you get), N 2, Club Six Groove, 6/23/2002, Y Note that you can put any date in for the 'album' and ignore everything other than the year, or you can research that data. Now, when you create a new album or playlist (collection of tarcks) you enter a new row in the Collections table, and build the Albums_Tracks (Collections_Tracks) table. Advantages here. With a specific Track (say #50), we can find out what collections it was used for in the following way: SELECT C.Title, C.Play_List FROM Collections as C, Collections_Tracks as CT WHERE C.Collection_ID = CT.Collection_ID AND CT.Track_ID = 50; You could also find when it was played (include the Date, and filter for Play_List), when it appeared in the performance, etc. To recreate the playlist, you would sort on the index. If you create new tables for each playlist, then you can't do any data crunching, or comparisons to see what songs you use most, or least, or paterns, or what ever... As a DJ, you might want to spend the extra time figuring out BPM for each track, and adding that to the track data, then you can get the computer help you look for good songs to tie together You know, you could do all of this via a text file.... Of course a spread sheet app would help you copy and paste, and keep things neat. Flat tables will rule the WORLD!!! Oh wait, that was FrameWork will rule the world. NOT! --------------------------------------------------------------------- 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 --------------------------------------------------------------------- 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
