Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Would it be possible to give a table in MySQL query multiple aliases?

If so how would I do this?

share|improve this question
2  
Hi, can you help me understand why you would want to do this? –  Derek Downey Apr 5 '13 at 13:32
add comment

2 Answers

up vote 1 down vote accepted

You can if you are using the table multiple times in different clauses and/or sub-queries like

SELECT SeniorManager = sm.displayName, Manager = m.displayName, Worker = w.displayName
FROM   people AS w
LEFT OUTER JOIN people AS  m ON  m.id = w.bossID
LEFT OUTER JOIN people AS sm ON sm.id = m.bossID

But you can not give the same use of a table more than one alias - the following for instance is invalid syntax:

SELECT a1.name, a2.name FROM people AS a1 AND a2

Also, you can not (in most DBMSs) use a table by both its real name and alias, so the following is invalid too:

SELECT people.name, a1.name FROM people AS a1 

If ause of an object is given an alias you must refer to that instance by that alias even if the real name is unambiguous within the query.

share|improve this answer
add comment

Yes you can.

For example: for a table such as this:

create table staff
(
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    Name varchar(100),
    supervisorId INT,
    FOREIGN KEY (SupervisorId) REFERENCES staff(id)
)

You can query it like so :

select a.name as EmployeeName, b.name as SupervisorName
from staff a,
 staff b
where 
 a.supervisorId = b.id

or

select a.name as EmployeeName, b.name as SupervisorName
from 
 staff a
 inner join staff b on a.supervisorId = b.id
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.