select two columns to join with one column 1

[stevetektip]

I've got a table with two id columns which are foreign keys into the same table:

table1
========
repID
asstRepID

table2 (names of reps)
======================
id
name

I want to retrieve a row that has the name of both the rep and asst rep, such as:

repID repName asstRepID asstRepName
---------------------------------------------
234 D. Duck 381 M. Mouse

I've tried joining as follows:

select repID, name as repName, asstRepID from table1 join table2 on table1.repID=table2.ID

which gives me the repName, but I'm getting syntax errors for anything I've tried to get the asstRepName with joins, sub queries, etc.

Any guidance is greatly appreciated.
Thanks!

 

[ClaireHCM]

Try this

select c.repid,c.repname,c.asstrepid,name as asstRepName from table2
inner join
(select repID, name as repName, asstRepID from table1 join table2 on table1.repID=table2.ID)as C
on C.asstrepid =table2.id