CODEWITHSUNDEEP
mysql
Chris
Chris

Reputation: 3698

MySQL, is there any way to use multiple alias?

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..

Upvotes: 4

Views: 13304

Answers (3)

codingbiz
codingbiz

Reputation: 26396

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

Upvotes: 2

Dan Graller
Dan Graller

Reputation: 325

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.

Upvotes: 6

Muhammad Saifuddin
Muhammad Saifuddin

Reputation: 1294

I would suggest to go with join OR defined each table alias to access their fields.

SELECT a.title, a.name AS Composers_Name, s.name AS Songwriters_name
FROM artist a, song s

But in your question songwriter name field is missing. Between you can follow this way to access it.

Upvotes: 0

Related Questions