Adolfo, Thanks for the info, but can you elaborate on it, 'cause basically I am just not getting the concept. No way no how can I get these joins to work.
mysql> describe songs; (this contains an ID field and the title of the song and the artist.) +--------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------+-------------+------+-----+---------+-------+ | ids | int(11) | | PRI | 0 | | | title | varchar(55) | YES | | NULL | | | artist | varchar(30) | YES | | NULL | | +--------+-------------+------+-----+---------+-------+ 3 rows in set (0.00 sec) mysql> describe album; (this contains an ID field and the name of the album the above songs came from.) +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | ida | int(11) | | PRI | 0 | | | name | varchar(35) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec) mysql> describe reference; (this contains the ID's that corrspond to the above two tables.) +-------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+---------+------+-----+---------+-------+ | ida | int(11) | | PRI | 0 | | | ids | int(11) | | PRI | 0 | | +-------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec) This table is in this format: The left column corresponds to the ID of the ablum and the right field correspond to the ID of the song. From the example below, you can see that song #1 appears on Albums 2, 3, 6, and 16. Song Album 1 2 1 3 1 6 1 15 2 7 2 21 2 23 So with all this information, how would I perform a select that would show the all the albums a particular song were on. (from the example above what would I need to do to show the album name and song title for song ID # 1 above). Sorry if this is long, I just wanted to make sure there is enough info. TIA Beauford ----- Original Message ----- From: "Adolfo Bello" <[EMAIL PROTECTED]> To: "'Beauford.2003'" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, December 02, 2002 7:33 AM Subject: RE: Table setup question > Try something like > CREATE TABLE album( ida int primary key , title varchar(n) not null ) ; > CREATE TABLE songs( ids intprimary key, song varchar(m) not null ) ; > CREATE TABLE albumsongs( > ida int not null, > ids int not null, > primary key(ida,ids), > foreign key(ida) references album(ida), > foreign key(ids) references songs(ids)); > > Querying for albums which have a songs is something like: > SELECT title,song FROM album INNER JOIN albumsongs ON ... > INNER JOIN songs ON ... WHERE song='your song name'; > > > > -----Original Message----- > > From: Beauford.2003 [mailto:[EMAIL PROTECTED]] > > Sent: Sunday, December 01, 2002 9:59 PM > > To: [EMAIL PROTECTED] > > Subject: Table setup question > > > > > > Hi, > > > > I have a database of albums by a group and I want to be able > > to search on this table to find out what songs are duplicated > > on what albums (there are > > 36+ albums). The problem is how do I set up the database. > > > > Example: > > > > Album1 has 3 songs. 1.song, 2.song, 3.song, 4.song > > Album2 has 4 songs. 4.song, 5.song, 6.song, 3.song > > Album3 has 4 songs. 7.song, 8.song, 1.song, 3.song > > > > So 3.song appears on all 3 albums. > > > > Currently I have it set up with two tables as shown below, > > but I am thinking there has to be a better way to do this > > than to duplicate the name of the song three, four, or five > > times in the table. > > > > Table AlbumName > > > > Album ID > > ==== == > > Album1 1 > > Album2 2 > > Album3 3 > > > > Table SongTitle > > > > Song ID > > === == > > 3.song 1 > > 3.song 2 > > 3.song 3 > > 7.song 3 > > etc. > > etc. > > > > So basically my search is - SELECT Album, Song FROM > > AlbumName, SongTitle WHERE AlbumName.ID=SongTitle.ID; > > > > Given the setup above, is there a way that I can put in the > > SongTitle.ID field that song appears on more than one album. > > Maybe something like: > > > > Song ID > > === == > > 3.song 1, 2, 3 > > > > But then what would my search be. > > > > Sorry for the length of this, but I am learning MySQL and > > trying to get a handle on all of it. My way works, but I'm > > sure there has to be a better way. > > > > Any thoughts are appreciated. > > > > TIA, Beauford > > > > > > > > --------------------------------------------------------------------- > > 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 > > --------------------------------------------------------------------- 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
