Joining tables in MySQL question (php)

Situation:

In my database I have a table called 'artists' and 'tags'.

Each artist has a set of tags, saved in a linking table 'artisttags'.

Each unique tag is saved in a table called 'tags'.

Problem

I would like to show all artists that have one (or more) tags in common with a given artist.

function getSimilarArtists($artist_id)
{
   $sql = "SELECT ...";
   $query = mysql_query($sql);
   while($artist = 开发者_如何学Gomysql_fetch_assoc($query))
   {
       $html .= "<li>".$artist['name']."</li>"    
   }
   print($html);
}

Tables

artists

id | name

artisttags

id | artist_id | tag_id    

tags

id | tag_name

Any help is welcome.

Thanks

Those outer joins in Mitosz's reply are really going to hurt - and will return every artist - not just those with "one (or more) tags in common". Use Inner Joins instead

SELECT similar.name, count(*) as commontags
FROM artists current, 
  artisttags curtags,
  artisttags simtags,
  artists similar
WHERE current.id=curtags.artist_id
  AND curtags.tag_id=simtags.tag_id
  AND simtags.artist_id=similar.id
ORDER BY count(*) DESC;

Of course, for a smarter indexing system you could apply scoring to each tag in the tags table (e.g. based on user votes or cardinality) and sort your results by SUM(tag.score).

SELECT DISTINCT a.name FROM artisttags at
LEFT JOIN artisttags at2
ON at2.tag_id = at.tag_id
LEFT JOIN artists a
ON at2.artist_id = a.id
WHERE at.id = '$artist_id'