Thanks for the help, but I just can't grasp the concept (I'm not even sure I have my table layouts the way they should be to have this work). I'll have to look into this further, but for the time being the way I have it is working.
Beauford ----- Original Message ----- From: "Adolfo Bello" <[EMAIL PROTECTED]> To: "'Beauford.2003'" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, December 04, 2002 4:28 PM Subject: RE: Table setup question > Let's assume you want the entire albums list which the song > "Day and Night" by Billie Holiday is in. > > SELECT t1.title,t1.artist,t3.name FROM sings t1 > INNER JOIN reference t2 ON t1.ids=t2.ids > INNER JOIN album t3 ON t2.ida=t3.ida > WHERE t1.title='Day and Night' AND t1.artist='Billie Holiday' > > (or "WHERE t1.ids=1" if you know the id of the song) > > Relationship is like: > > [songs] [album] > \\ // > [reference] > (this later one contains the list of songs of each album) > > This way you treat songs and album separately and link them > through another table so you don't have any limit on the number > of albums a song can belong to. > > If you know the id of a song all you have to do is > > Adolfo > > > -----Original Message----- > > From: Beauford.2003 [mailto:[EMAIL PROTECTED]] > > Sent: Thursday, December 05, 2002 3:42 PM > > To: Adolfo Bello > > Cc: [EMAIL PROTECTED] > > Subject: Re: Table setup question > > > > > > 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 > > mysql> 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 > > mysql> 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
