CODEWITHSUNDEEP
mysqlsql
Ben
Ben

Reputation: 10156

Using a join to return multiple values of the same column?

The problem is that I'm using a table that has more than one reference to another table; basically I'm storing three different values for the original artist of a song, if it is featuring another artist, and if it is a remix or not. Each of these link to the artist table and what I want is to be able to return the artist name (no other data) for each of these columns. I have this so far:

SELECT `songName`, `songTrackNumber`, `artist`.`artistName`,
songFeaturingArtist, songRemixByArtist
FROM `song`
LEFT JOIN artist ON songArtist = artistID

I'm not entirely sure how to convert the other two fields from their numeric ID to the name of the artist. Any pointers would be greatly appreciated.

Upvotes: 1

Views: 2711

Answers (2)

John Woo
John Woo

Reputation: 263843

Join table Artist on table song three times to get the artistName for all the other columns on table song.

SELECT  a.songName, 
        a.songTrackNumber, 
        b.artistName AS ArtistName,
        c.artistName AS FeaturingArtist,
        d.artistName AS RemixByArtist
FROM    song a
        LEFT JOIN artist b ON a.songArtist = b.artistID
        LEFT JOIN artist c ON a.songFeaturingArtist = c.artistID
        LEFT JOIN artist d ON a.songRemixByArtist = d.artistID

Upvotes: 3

Ja͢ck
Ja͢ck

Reputation: 173642

Just keep adding joins, but by using table aliases to keep MySQL happy:

SELECT `songName`, `songTrackNumber`, 
    `a1`.`artistName` AS mainArtistName,
    `a2`.`artistName` AS songFeaturingArtistName,
    `a3`.`artistName` AS songRemixByArtistName
FROM `song`
LEFT JOIN artist a1 ON songArtist = a1.artistID
LEFT JOIN artist a2 ON songFeaturingArtist = a2.artistID
LEFT JOIN artist a3 on songRemixByArtist = a3.artistID

For clarity you could change the aliases to something more useful than a simple numeric suffix :)

Upvotes: 3

Related Questions