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
