[RESOLVED] MySQL aliasing problem, re-using same table twice

[Chris_96_WS6]

Creating a simple request logging system in this case. In the requests table, I log the person making a request, and the person that is the subject of the request (the person the particular request affects).

I need to display each person's name in a table of outstanding requests. They are referenced as "RequesterID" and "PersonID", both of which correspond to a PersonID in the Persons table.

So my query looks like this at the moment:

SELECT Persons.LAST_NAME, Persons.FIRST_NAME FROM Requests
LEFT OUTER JOIN Persons ON Requests.PersonID = Persons.PersonID

So I really need to make a second select of the name columns from Persons table with a join where Requests.RequesterID = Persons.PersonID

I am just lost on the syntax to do this.

I see in my little SQL cheat book that you can alias by explicitly naming the columns you want to select...

So would I do this?

SELECT 
Persons.LAST_NAME as Person_Last, 
Persons.FIRST_NAME as Person_First,
Persons.LAST_NAME as Requester_Last, 
Persons.FIRST_NAME as Requester_First
FROM Requests
LEFT OUTER JOIN Persons ON Requests.PersonID = Persons.PersonID
LEFT OUTER JOIN Persons ON Requests.RequesterID = Persons.PersonID

But how would MySQL know which to assign to which?

[Chris_96_WS6]

Aha,

More experimentation yeilded the result I needed. Hope this helps somebody else:

Proper Joined aliasing example:

SELECT 
Per.LAST_NAME as Per_Last,
Per.FIRST_NAME as Per_First,
Req.LAST_NAME as Req_Last,
Req.FIRST_NAME as Req_First,
FROM Requests
LEFT OUTER JOIN Persons as Per ON Requests.PersonID = Per.PersonID
LEFT OUTER JOIN Persons as Req ON Requests.RequesterID = Req.PersonID