-2

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

enter image description here

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

Why is it not working?

Akina
  • 39,301
  • 5
  • 14
  • 25
puradrogasincortar
  • 121
  • 1
  • 8
  • 3
    You should think about `ON` conditions as well. – Gordon Linoff Feb 18 '20 at 19:29
  • @GordonLinoff What is the difference between applying the Track.genre_id = Genre.genre_id with an ON and a WHERE? Anyways it doesn't work so I guess this may not be the right condition to apply, but why it's not? – puradrogasincortar Feb 18 '20 at 19:35
  • *What is the difference between applying the Track.genre_id = Genre.genre_id with an ON and a WHERE?* In MySQL - no difference. – Akina Feb 18 '20 at 19:38
  • At this point, I feel a couple of hours in the company of a good introductory book or tutorial will pay dividends – Strawberry Feb 18 '20 at 19:40
  • Provide a fiddle. Now your problem is not clear. – Akina Feb 18 '20 at 19:40
  • Is this what you're aiming for? https://stackoverflow.com/questions/18870326/comma-separated-results-in-sql – Bethany Feb 18 '20 at 19:44
  • Which is it? "I want to find every genre that 'John Coltrane' plays" or " just 'Jazz' which is what I'm seeking..."? – Morpheus Feb 18 '20 at 20:11

1 Answers1

0

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'
Mathew Schlabaugh
  • 181
  • 4