Join Two Tables With Two Columns SQL Server 2008 R2

I have two tables that I would like to join together. One is a table with various bits of information and one is a dimension view, a definitions table of sorts. The table with the

Solution 1:

You join back into the table again, so it looks as though you're FROMing that same table twice (one for the attending doctor lookup, one for the admitting doctor lookup).

SELECT a.doc_name as attending_name, 
       b.somefield, 
       a2.doc_name as admitting_name

FROM doctors a, 
     someothertable b, 
     doctors a2

WHERE a.doc_id = b.attending_doc_id
  AND a2.doc_id = b.admitting_doc_id
  AND b.record_id = <whatever>

and your inner join for a targets the first doctor, the join for a2 targets the second doctor.

Pardon the pseudo-code, but I think you get the idea. You'll notice that a and a2 are both getting the doc_name field from the doctors table, but they're joined to the different IDs off the b table.