I apologize for not including that in my original post. Here's my create table statement: For albums, artists, tracks: CREATE TABLE artists (artists VARCHAR(50), id MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY)
For albums_artists, artists_tracks, tracks_albums: CREATE TABLE albums_artists (album_id MEDIUMINT NOT NULL, artist_id MEDIUMINT NOT NULL) Insert statement: LOAD DATA LOCAL INFILE "albumsartists.txt" INTO TABLE albums_artists For tracks, albums, and songs, their .txt files are simply a one column list. The other column is auto_increment. So a sample from tracks: 11am 1979 3 Libras 32 Flavors 4 Degrees 46 & 2 99 Red Balloons A Place For My Head A Place In The Sun Abracadabra >From albums: 311 14:59 8 (unknown) <---generic, unknown album. really a record. 13 Ways To Bleed Onstage 24/7 40oz To Freedom A Boy Named Goo A Few Small Repairs A Little South Of Sanity >From artists: 311 10,000 Maniacs 3 Doors Down 311 A Perfect Circle AC/DC Adema Aerosmith Afghan Whigs AK1200 For albumsartists.txt, tracksalbums.txt, and artiststracks.txt, they're a two-column, tab delimited text file. Examples from albumsartists.txt: 1 1 2 258 3 35 4 14 4 76 4 167 4 230 5 53 6 223 7 256 >From tracksalbums.txt: 1 237 2 221 3 222 4 41 5 354 6 16 7 315 8 176 9 12 10 2 >From artiststracks.txt: 1 15 1 19 1 250 2 40 3 38 3 138 3 281 3 303 3 363 5 3 5 62 I thought 2 links would be enough as well, but I tried this query using just the tracks_albums and albums_artists table and couldn't get it working. For example, using this: SELECT tracks.tracks, albums.albums, artists.artists FROM tracks, albums, artists, tracks_albums, albums_artists 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 I get 718 returns instead of 614. If more than one artist is linked to one album, only the song from the first artist shows in the tracks column and it shows for all artists (no other songs by the different artists appear). Am I just missing something in this query? Thanks for your help, Jason -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Sammy Lau Sent: Wednesday, May 22, 2002 8:18 PM To: Jason Soza Cc: [EMAIL PROTECTED] Subject: Re: Query Problem 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. > --------------------------------------------------------------------- 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
