How to find every genre from an artist in a music database in MYSQL?

So I’ve got a database with 4 tables: artist, genre, track and album. Track table points in a many to one way to Genre table, and so on with Track to Album, and Album to Artist.

Suppose I want to find every genre that ‘John Coltrane’ plays, so I thought about saying

SELECT DISTINCT Artist.name, Genre.name 
FROM Artist 
JOIN Genre 
JOIN Album 
JOIN Track  
WHERE Artist.name = 'John Coltrane' 
  AND Track.genre_id = Genre.genre_id

But this just gives me

i.e. ALL genres jointed to John Coltrane, instead of just ‘Jazz’ which is what I’m seeking…

Why is it not working?

Answer

Since you didn’t show all the table columns which would be used to join tables together. I will make some assumptions for you. I will be more accurate if I notice that you updated the topic and I will update my answer when I see it. Where I am incorrect about the column name that I join on just substitute the actual column with the one I put here and it will do what you are trying to do. 😉

SELECT DISTINCT ART.name,
       Genre.name
  FROM Artist ART
  JOIN Album ALB ON ART.artist_id = ALB.artist_id
  JOIN Track TRK ON ALB.album_id = TRK.album_id
  JOIN Genre G ON TRK.genre_id = G.genre_id
 WHERE ART.name = 'John Coltrane'