mysql many-to-many query issue

i have many to many database and i'm there is a query that i just got stuck with and cant do it.

i have 4 tables Artists, Tracks, Albums, Clips

now i'm on the artist page so i need to get them by the artist page, i already got all of them, but not the way i want them.

because some tracks, albums, clips belong to other artists as well (duet) and i need to display their name.

but the problem is that i'm selecting using the artist id so my GROUPC_CONCAT function wont work here is the query that gets the artist albums.

   SELECT  al.album_name, GROUP_CONCAT(a.artist_name SEPARATOR ', ') AS 'artis开发者_如何学编程t_name'

   FROM 
     Albums al
   LEFT JOIN 
     ArtistAlbums art ON art.album_id = al.album_id
   LEFT JOIN 
     Artists a on a.artist_id = art.artist_id
   WHERE 
     a.artist_id = 10
   GROUP BY 
     al.album_id

one of the albums have two artists attached to it, but it does not get the other artist name.

when i select by the album_id i get the two artists.

please note that i'm new to mysql and i did not find any answers on this particular problem almost no resources on many-to-many querying.

how can i tackle this problem.?

any resources or books on many-to-many that show how to deal with the database on the application layer will be much appreciated, thanks in advance.

Think of table aliases as really being row aliases. That is, for purposes of expressions in the WHERE clause and the select-list, the alias refers to a single row at a time.

Since you've created a condition such that a.artist_id = 10, then only rows for that artist match the condition. What you really want is to match all artists on an album given that one artist is artist_id = 10.

For that, you need another join, so that the row where artist_id = 10 is matched to all the rows for that respective album.

SELECT  al.album_name, GROUP_CONCAT(a2.artist_name SEPARATOR ', ') AS `artist_name`
FROM 
  Albums al
INNER JOIN 
  ArtistAlbums art ON art.album_id = al.album_id
INNER JOIN 
  Artists a on a.artist_id = art.artist_id
INNER JOIN 
  ArtistAlbums art2 ON art2.album_id = al.album_id
INNER JOIN 
  Artists a2 on a2.artist_id = art2.artist_id
WHERE 
  a.artist_id = 10
GROUP BY 
  al.album_id

P.S.: I've also replaced your use of LEFT JOIN with INNER JOIN. There's no reason you needed LEFT JOIN, since you're explicitly looking for albums that have a matching artist, not all albums whether or not it has artist 10. Review the meaning of different types of join.

Re your followup question:

I don't know of a book specifically about many-to-many queries. I'd recommend books like:

• SQL Antipatterns Volume 1: Avoiding the Pitfalls of Database Programming, which is my own book and it does cover many-to-many tables.
• SQL and Relational Theory to understand joins better.
• Joe Celko's SQL Programming Style, which imho is Joe Celko's best book.