CODEWITHSUNDEEP
mysqlsql
Totte Karlsson
Totte Karlsson

Reputation: 1341

SQL Query for getting album information knowing only the track_id

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..

Upvotes: 0

Views: 2340

Answers (2)

ScaisEdge
ScaisEdge

Reputation: 133380

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

Upvotes: 0

Horaciux
Horaciux

Reputation: 6477

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'

Upvotes: 1

Related Questions