Query Assistance -- making one field into two -- 1

[link9]

Hello all --

Well, I was trying to make a detailed description of my question, and it didn't turn out very well, so I figured, "a picture is worth a thousand words"...

http://consumermetrics.com/paul/relationships.gif

That's just a snapshot of the issue at hand, so you get a feel for the relationships that exist -- and here's the question...

See the 'ae' and 'pm' columns there?? And the 'employee' table that they are linked to? That relationship is via 'empID', which is a number in the project list table, but equates to 'initials' in the 'employee' table. That's what I want for both fields in my view.

See? It's simple enough when all I want is a single link, but in the view I'm constructing, I want an 'ae' column and a 'pm' column, but both are to have the initials of the employee, rather than the integer 'empID' that exists in the projectList table...

I hope this is clear (and simple) enough for you guys... and as always, I very much appreciate the assistance.

Thanks!
Paul Prewett

 

[redlam]

hi paul.
not sure if this is the best way but why not try something like this?

select projectnum,
ae.initials as ae_initials,
pm.initials as pm_intials
from projectlist pl left join
employee ae on
pl.ae = ae.emp_id left join
employee pm on
pl.pm = pm.emp_id

 

[link9]

Thank you, redlam --

It worked the trick.