Thank you for that , I just needed somewhere to start from and am new to doing something like this.
-----Original Message----- From: Neil Zanella <[EMAIL PROTECTED]> To: "Barry.J.Rumsey" <[EMAIL PROTECTED]> Date: Sun, 9 Dec 2001 00:29:40 -0330 (NST) Subject: Re: Join > > Before you proceed I would advise you to revise your relational model. > For instance why do you have both id and artistid in both tables? > Can more than one artist sing or participate in the production > of the same song? If yes then your database should probably look like: > > Artists(artist_id, artist_name) PRIMARY KEY artist_id AUTO_INCREMENT > Sings(artist_id, song_id) PRIMARY KEY (artist_id, song_id) > FOREIGN KEY artist_id REFERENCES Artists > FOREIGN KEY song_id REFERENCES Songs > Songs(song_id, song_name) PRIMARY KEY song_id AUTO_INCREMENT > > Then to search for all songs by all artists named Joe Doe you would do: > > SELECT song_name > FROM Artists, Sings, Songs > WHERE Artists.artist_id = Sings.artist_id > AND > Sings.song_id = Songs.song_id > AND > artist_name = 'Joe Doe'; > > I leave it as an exercise for you to modify this model to account for > a group of Artists being part of a particular Band with the band names > also stored in the database. > > Bye, > > Neil > > On Sun, 9 Dec 2001, Barry.J.Rumsey wrote: > > > I have a database with two tables: > > table1 id, artistid, artist > > table2 id, artistid, songtitle > > What I would like to know is how do I join these two. artistid in > both > > of them have the same value. What I'm after in the end is to be able > to > > search by artist and return a list of songtitles from that artist. > I'm > > using Roxen as my server on win32 system. > > > --------------------------------------------------------------------- > 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
