Mysql Query help

[Anon]

I was wondering if I could get a little help writing a query in mysql.

I have a search engine form that allows users to check off a list of criteria they want the music artist to have (plays drums, plays guitar, reads sheet music, etc). All this information is stored in a table that looks something like this:

artistTalent TABLE:

artistID | talentID

2 | 3
4 | 5
2 | 8
...

and the talent table (and artist table follows similar format) looks something like this:

talent TABLE:

talentID | talentName

1 | drums
2 | sheet music
....

my query looks like this:

SELECT artistID,artistName from artists, talent WHERE artists.artistID = talent.talentID

My problem is that if the user clicks off three talents they want the person they're searching for to have, then they get three results if the one artist has three talents that were selected.

I am guessing I need to do some sort of JOIN but am not 100% sure how.

Any help would be appreciated

[Anon]

You are doing a join currently, but it doesn't seemt o make sense. Why would you want to compare an artistID with a talentID, instead your SQL should look like this:

SELECT
artist.artistID,artistName
FROM
artists, artistTalent, talent
WHERE
artists.artistID = artistTalent.artistID AND
artistTalent.talentID = talent.talentID AND
talent.talentID = $$YOUR_CRITERIA$$

Make sure you replace $$YOUR_CRITERIA$$ with whatever talent they've chosen to search for.

[Anon]

sorry, it should have said artistTalent not talent. The join should be working, but I am getting too many results..query should have been:

SELECT artistID,artistName from artists, talent,artistsTalent WHERE artists.artistID = artistsTalent.artistIDID AND artistsTalent.talentID = talent.talentID

If an artist has three talents in artistsTalent and the user is searching for those three talents the artists Name will appear three times in the query results.

[Anon]

use select DISTINCT

[Anon]

I tried using DISTINCT as well. 🙂

Oh well... I'll start from nothing and see if I can get it to work.

Adam wrote:

use select DISTINCT