0

Having a simple database table structure for three tables;

artist, album, tracks

where the table album has a foreign key, named artist_id, to a field named id in the artist table, and table tracks having a foreign key, named album_id to a field id in table album. The table tracks, do have a unique id for each record, named track_id, as well as a foreign key, album_id, to the albums table

How does a query look that selects artist and album information, from knowing only the track_id?

I tried the following, but it returns more than one record, which is not right.

SELECT *
FROM artist
INNER JOIN albums ON artist.id = albums.artist_id
INNER JOIN tracks ON tracks.track_id = '4021'

Something is obviously missing..

Totte Karlsson
  • 1,261
  • 1
  • 20
  • 55
  • You should really use the `where` for limiting the data and the `on`s for the `join` should be joined. – chris85 Aug 01 '17 at 19:45
  • Aren't the 'on's joined? If not, what do you mean by that? – Totte Karlsson Aug 01 '17 at 19:50
  • The `on` is how the data is joined. The `where` is what data should be returned. From the manual: `Generally, the ON clause serves for conditions that specify how to join tables, and the WHERE clause restricts which rows to include in the result set.` - https://dev.mysql.com/doc/refman/5.7/en/join.html – chris85 Aug 01 '17 at 20:22

2 Answers2

1

Use where to filter, and added a missing join specification

SELECT *
FROM
artist
    INNER JOIN
albums
    ON artist.id = albums.artist_id
    INNER JOIN 
tracks
    ON tracks.album_id=albums.id
where tracks.track_id = '4021'
Horaciux
  • 6,322
  • 2
  • 22
  • 41
  • it seems one can use 'AND' instead of the WHERE statement. What would be the difference? – Totte Karlsson Aug 01 '17 at 19:47
  • with today engines, there will be no difference, they are smart enough to move it as needed. Just for readability reasons, I use it this way. – Horaciux Aug 01 '17 at 19:53
  • see: https://stackoverflow.com/questions/354070/sql-join-where-clause-vs-on-clause – Horaciux Aug 01 '17 at 19:53
  • A follow up question, In the above query, two columns are returned with the same name; artist.id and album.id. Is there a simple way to get retruned column names prefixed with their table name? – Totte Karlsson Aug 01 '17 at 20:46
  • Not sure about MySQL, in SQL-Server you need to type each field alias, or can be added dynamically, but it a new story. – Horaciux Aug 01 '17 at 20:56
0

Missing the condition for join tracks and albums ON tracks.album_id = albums.id 

SELECT *
FROM artist
INNER JOIN albums ON artist.id = albums.artist_id
INNER JOIN tracks ON tracks.album_id = albums.id 
      and  tracks.track_id = '4021' AND
ScaisEdge
  • 131,976
  • 10
  • 91
  • 107