SQL Problem: Retrieving information On Same Table via Aliases

[michaelzip]

Hi,

I am having trouble with retrieving records on my database.
My layout was this..

UsersList
  Username
  Firstname
  LastName
  Role

Records
  TheUser
  ApprovedBy
  DateApproved

The first text are the table names while the indented once are the field names.
I have a table named Records. TheUser and ApprovedBy was referring to the Username under the UsersList table.

Now my problem was, when I retrieve the records by aliases. I need to use the first name and last name of the users to generate report.

Example:

UsersList
	mzip
	Michael
	Zip
	Supervisor

	xtian
	Christian
	Holmes
	Trainee

Records
	xtian
	mzip
	1/22/2012 4:53:00 PM

If I will use this query, it will just show similar record.

SELECT (LastName + ', ' + FirstName) AS theTrainee, (LastName + ', ' + FirstName) AS PersonInCharge FROM UsersList
INNER JOIN Records ON UsersList.Username = Records.theUser

theTrainee      PersonInCharge 
Zip, Michael    Zip, Michael

What I need is

theTrainee      PersonInCharge 
Zip, Michael    Holmes, Christian

Any Ideas?
Michael

[adam_k]

This should do the trick:

select a.LastName + ', ' + a.Firstname as theTrainee, b.LastName + ', ' + b.Firstname as PersonInCharge 
from Records r inner join UsersList a on r.TheUser = a.Username 
inner join UsersList b on r.ApprovedBy = b.Username

I haven't tested it, so it might contain typos.

[michaelzip]

It works! You're a genius mate!