MySQL, is there any way to use multiple alias?

Question

Given we have a database with the tables:
"artist" (columns: name, id) and
"song" (title, composers_id, songwriters_id).

I want to print the songs with the composers name and songwriters name. I have succeeded in printing the composers name only with:

SELECT title, name AS Composers_Name 
  FROM artist, song 
    WHERE song.composers_id = artist.id;

I am failing to fetch the songwriters name..
What I tried was this:

SELECT title, name AS Composers_Name, name AS Songwriters_name
  FROM artist, song 
    WHERE song.composers_id = artist.id AND song.songwriters_id = artist.id;

But this returns all the songs that the composers and songwriter is the same person. I am thinking of using JOIN but I'm not sure how..

Answer 1

You have to select from the table artist 2 times.

select s.title, a1.name AS Composer, a2.name as songwriter
from song s, artist a1, artist a2 
where s.composers_id = a1.id and s.songwriters_id = a2.id;

assuming that Composers and Songwriters are both stored in the table artist.

Answer 2

Use left join

SELECT s.title, c.name AS Composers_Name, a.name AS Songwriters_name
  FROM song s
  LEFT JOIN artist a ON s.songwriters_id = a.artistID
  LEFT JOIN artist c ON s.composers_id = c.artistID