Query joined table twice

[menkes]

I know the answer to this is not terribly difficult -- except I seem to not be able to get it.

I am querying a table that has multiple fields that refer to other tables. Two of the fields refer to the same table, but different records.

Example:
Table1, Field1 = Sales Rep ID : get name from Coworker table
Table1, Field2 = CSR ID : get name from Coworker table

Here is the query:

SELECT t1.client_id, t1.dpauto, t1.client_name,t2.fac_abbrev, t3.lob_abbrev, t4.ind_name, (Left(t5.fname,1) + t5.lname) AS ae_name,(Left(t6.fname,1) + t6.lname) AS pm_name 
FROM tblPS_CLIENT t1
JOIN tblFACILITY t2 ON t1.fac_id = t2.fac_id
JOIN tblLOB t3 ON t1.lob = t3.lob_id
JOIN tblINDUSTRY t4 on t1.industry = t4.ind_id
JOIN tblCOWORKER t5 ON t1.ae_id = t5.ps_id
JOIN tblCOWORKER t6 ON t1.pm_id = t6.ps_id
ORDER BY t1.client_name

The problem is the Join on t6. Since I've already joined that table, it does not work. I knew it wouldn't, but just cannot seem to grasp how to get that second name out of the same table. I'm sure it's a nested select, but...

Any help?

 

[ESquared]

Your query looks fine. What isn't working?

For what it's worth, it might be useful to you to choose yor aliases based on the name of the table you are aliasing. This makes it easier to use them or read the select portion of the query later. For example, make tblFACILITY F and tblIndustry I. Then when I see I.ind_name I know exactly what table it's coming from without looking.

Is it possible that the problem is that there is sometimes a missing Sales Rep or CSR? In that case, you might have some luck with LEFT JOINs.

-------------------------------------
A sacrifice is harder when no one knows you've made it.