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

Question

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.

John Woo · Accepted Answer · 2013-01-18 14:36:15Z

3

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

answered Jan 18, 2013 at 14:36

John Woo

265k70 gold badges509 silver badges500 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Ben Over a year ago

Brilliant, I wasn't aware you could left join and give the table a different alias. Thank you!

John Woo Over a year ago

you're welcome. By the way, if these columns (songArtist, songFeaturingArtist, songRemixByArtist) are non-nullable, use INNER JOIN since it is faster than LEFT JOIN.

John Woo Over a year ago

one more thing, backtick aren't required on the column names even if you are using reserved keyword because an alias was provided on every table names.

Ben Over a year ago

Going to use LEFT JOIN because the other columns are usually NULL, but of course important if they contain a value. Thanks for your help :-)

spencer7593 Over a year ago

@JW: excellent point about qualifying column references with a table alias (a.artistName). That's the normative pattern for ANY query that references more than one table. A big advantage is that it insulates a working query from breaking (throwing an "ambiguous column" error) when columns are added to tables referenced in the query.

|

Ja͢ck · Accepted Answer · 2013-01-18 14:35:17Z

3

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 :)

answered Jan 18, 2013 at 14:35

Ja͢ck

174k39 gold badges269 silver badges317 bronze badges

1 Comment

Ben Over a year ago

Thanks for the speedy answer! +1

Collectives™ on Stack Overflow

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

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related