MySQL relational view - join?

Question

I'm relatively new to MySQL and I'm having no end of difficulty trying to work out how to do this. I've researched joining and such but I'm not really sure how to apply this.

I have three tables:

Artists Table ID, Name, Description

Albums Table ID, Title, Year, Artist_ID

Tracks Table ID, Title, Album_ID

Tracks are assigned to albums and albums are assigned to artists. (I did this through the relation view in phpmyadmin, engine is InnoDB).

What I'm trying to do is query ALL of the tracks for a particular artist (from their ID).

Is anyone able to point me in the right direction? Thanks in advance!

Answer 1

Use:

SELECT t.title
  FROM TRACKS t
  JOIN ALBUMS al ON al.id = t.album_id
  JOIN ARTISTS a ON a.id = al.artistid
 WHERE a.name = ?

If you already have the artists.id value:

SELECT t.title
  FROM TRACKS t
  JOIN ALBUMS al ON al.id = t.album_id
 WHERE al.artist_id = ?

Answer 2

SELECT track.* from artists,albums,tracks where artist.ID=123 AND artist.id=albums.artist_ID AND albums.id=tracks.Album_ID

Answer 3

You may want to try the following:

SELECT      tracks.title
FROM        tracks
INNER JOIN  albums ON (albums.id = tracks.album_id)
INNER JOIN  artists ON (artists.id = albums.artist_id)
WHERE       artists.name = 'A particular arist name';

Note that JOIN is a synonym for an INNER JOIN. We're basically using simple join syntax, using the inner join operation, which is probably the most common join type. I hope this gets you going in the right direction.

Answer 4

Using the implicit join syntax:

SELECT * FROM Tracks, Artists, Albums
    WHERE Tracks.Album_ID=Albums.ID AND Albums.Artist_ID=Artists.ID
    AND Artists.Name='foo'

Explicitly:

SELECT * FROM Artists
    LEFT JOIN Albums ON Artists.ID=Albums.Artist_ID
    LEFT JOIN Tracks ON Tracks.Album_ID=Albums.ID
    WHERE Name='foo'

Or, with nested subqueries:

SELECT * FROM Tracks WHERE Album_ID IN
    (SELECT ID FROM Albums WHERE Artist_ID IN
        (SELECT ID FROM Artists WHERE Name='foo'))`

All three of these are identical (in the absence of NULL values). I recommend using the second one, as it is what seems to be selected by most SQL experts.