Three-way inner join with an additional table

Question

I have a music database with the following tables:

Artists (ID, ArtistName)
Albums (ID, ArtistID, AlbumName)
Performers (ID, ArtistID, PerformerName)
AlbumPerformers (ID, AlbumID, singer, guitar, bass, drums)

This is a practice schema as I'm learning MySQL so I might not have done the best job in setting it up, but I wanted to have as many tables as possible to give me more options.

I want to come up with an inner join query that will give me the artist, album and name of each position in the band.

The following gives me that, but puts the PerformerID instead of the Performer name:

    SELECT artists.Artist, albums.AlbumName, albumperformers.singer
    FROM artists
    INNER JOIN albums ON artists.artist_id=albums.artist_id
    INNER JOIN albumperformers ON albumperformers.album_id=albums.album_id;

How can I structure this so that it gives me the Performer name and not ID?

Thanks.

Answer 1

You need to perform additional JOINs to solve the singer, bass, guitar and drums fields, which appear to be a FKs to Performers.ID.

I've tested with the following table schemas:

CREATE TABLE Artists (ID INT, ArtistName VARCHAR(255));
CREATE TABLE Albums (ID INT, ArtistID INT, AlbumName VARCHAR(255));
CREATE TABLE Performers (ID INT, ArtistID INT, PerformerName VARCHAR(255));
CREATE TABLE AlbumPerformers (ID INT, AlbumID INT, singer INT, guitar INT, bass INT, drumer INT);

The following statement:

SELECT 
Artists.ArtistName, 
Albums.AlbumName, 
PerformerSinger.PerformerName as Singer, 
PerformerDrumer.PerformerName as Drumer, 
PerformerGuitar.PerformerName as Guitar, 
PerformerBass.PerformerName as Bass 
FROM Artists 
INNER JOIN Albums ON Albums.ArtistID = Artists.ID 
INNER JOIN AlbumPerformers ON AlbumPerformers.AlbumID = Albums.ID 
INNER JOIN Performers AS PerformerSinger on AlbumPerformers.singer = PerformerSinger.ID 
INNER JOIN Performers AS PerformerDrumer on AlbumPerformers.drumer = PerformerDrumer.ID 
INNER JOIN Performers AS PerformerGuitar ON AlbumPerformers.guitar = PerformerGuitar.ID 
INNER JOIN Performers AS PerformerBass ON AlbumPerformers.bass = PerformerBass.ID;

Produces the desired result:

+------------+-----------+----------------+--------+-------------+-----------------+                                                                                         
| ArtistName | AlbumName | Singer         | Drumer | Guitar      | Bass            |                                                                                         
+------------+-----------+----------------+--------+-------------+-----------------+                                                                                         
| Beatles    | Revolver  | Paul McCartney | Ringo  | John Lennon | George Harrison |                                                                                         
+------------+-----------+----------------+--------+-------------+-----------------+