Join two tables with two columns SQL Server 2008 R

Question

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 desired information looks like this

VISIT ID | ATN_DR_NO | ADM_DR_NO |...
12345678 | 987654    | 123456    |...

Where the ATN_DR_NO is the Attending Doctor id number and the ADM_DR_NO is the admitting Doctor id. I have a second table that has the Doctor ID numbers and their corresponding names like so

src_prct_no | pract_rpt_name | ...
987654      | Dr. A          | ...
123456      | Dr. B          | ...
...

What I am trying to accomplish is to obtain a results in a table where I have the following

VISIT ID | ATN_DR_NO | ATN DR NAME | ADM_DR_NO | ADM DR NAME
12345678 | 987654    | DR. A       | 123456    | DR. B

I don't know how to join on multiple columns.

Here is the query I am working with in whole:

SET ANSI_NULLS OFF
GO
-- VARIABLE DECLARATION AND INITIALIZATION
DECLARE @SD DATETIME;
DECLARE @ED DATETIME;
SET @SD = '2014-01-01';
SET @ED = '2014-02-01';

/*
-----------------------------------------------------------------------
THIS QUERY WILL GET ALL THE FRONT END INFORMATION REQUIRED FOR HOSIM
AND INSERT IT INTO A TABLE THAT WILL GET MATCHED UP WITH THE DISCHARGE
ORDERS TABLE
-----------------------------------------------------------------------
START OF QUERY 1
-----------------------------------------------------------------------
*/
-- TABLE DECLARATION
DECLARE @T1 TABLE (
    [ENCOUNTER ID]               VARCHAR(200)
    , /*NEW*/MRN                 VARCHAR(20)    
    , /*NEW*/DOB                 DATETIME
    , /*NEW*/GENDER              VARCHAR(10)
    , FINANCIALCLASSORIG         VARCHAR(200)
    , [ADMIT DATE]               DATE
    , [ADMIT TIME]               TIME
    , [ADMIT FROM]               VARCHAR(200)
    , [DISCHARGE DATE]           DATE
    , [DISCHARGE TIME]           TIME
    , /*TEST*/[ADMITTING MD NO]  VARCHAR(200)
    , /*TEST*/[ADMITTING MD]     VARCHAR(200)
    , /*TEST*/[ATTENDING MD NO]  VARCHAR(200)
    , /*TEST*/[ADTTENDING MD]    VARCHAR(200)
    , [MS DRG]                   VARCHAR(200)
    , LOS                        VARCHAR(20)
    , [ADMIT PATIENT STATUS]     VARCHAR (10)
    , [DISCHARGE PATIENT STATUS] VARCHAR(10)
    , [DISCHARGE DISPOSITION]    VARCHAR (200)
    , [DISCHARGE UNIT]           VARCHAR (200)
)
-- WHAT GETS INSERTED INTO @T1
INSERT INTO @T1
SELECT
A.[VISIT ID]
, A.MRN
, A.DOB
, A.GENDER
, A.FINANCIALCLASSORIG
, A.[ADMIT DATE]
, A.[ADMIT TIME]
, A.[ADMIT FROM]
, A.[DISCHARGE DATE]
, A.[DISCHARGE TIME]
, A.[ADMITTING DR NO]
, A.[ADMITTING DR]
, A.[ATTENDING DR NO]
, A.[ATTENDING DR]
, A.[MS DRG]
, A.LOS
, A.[ADMIT PATIENT STATUS]
, A.[DISCHARGE PATIENT STATUS]
, A.[DISCHARGE DISPOSITION]
, A.[DISCHARGE UNIT]
-- END @T1 INSERT SELECTION

-- WHERE IT ALL COMES FROM
-- COLUMN SELECTION
FROM (
    SELECT DISTINCT PAV.PtNo_Num        AS [VISIT ID]
    , /*NEW*/PAV.Med_Rec_No             AS MRN
    , /*NEW*/PAV.Pt_Birthdate           AS DOB
    , /*NEW*/PAV.Pt_Sex                 AS GENDER
    , PD.pyr_name                       AS [FinancialClassOrig]
    , CAST(PAV.Adm_Date AS DATE)        AS [ADMIT DATE]
    , CAST(PAV.vst_start_dtime AS TIME) AS [ADMIT TIME]
    , PAV.Adm_Source                    AS [ADMIT FROM]
    , CAST(PAV.Dsch_Date AS DATE)       AS [DISCHARGE DATE]
    , CAST(PAV.Dsch_DTime AS TIME)      AS [DISCHARGE TIME]
    , /*TEST*/PAV.Adm_Dr_No             AS [ADMITTING DR NO]
    , /*TEST*/PDV.pract_rpt_name        AS [ADMITTING DR]
    , /*TEST*/PAV.Atn_Dr_No             AS [ATTENDING DR NO]
    , /*TEST*/PDV.pract_rpt_name        AS [ATTENDING DR]
    , PAV.drg_no                        AS [MS DRG]
    , PAV.Days_Stay                     AS [LOS]
    , 'I'                               AS [ADMIT PATIENT STATUS]
    , 'I'                               AS [DISCHARGE PATIENT STATUS]
    , DDM.dsch_disp_desc                AS [DISCHARGE DISPOSITION]
    , VR.ward_cd                        AS [DISCHARGE UNIT]

    -- FROM DB(S)
    FROM smsdss.BMH_PLM_PtAcct_V PAV
         JOIN smsdss.pract_dim_v PDV
         ON PAV.Adm_Dr_No = PDV.src_pract_no
         /*TEST*/AND PAV.Atn_Dr_No = PDV.src_pract_no
         JOIN smsdss.pyr_dim PD
         ON PAV.Pyr1_Co_Plan_Cd = PD.pyr_cd
         JOIN smsmir.vst_rpt VR
         ON PAV.PtNo_Num = VR.acct_no
         JOIN smsdss.dsch_disp_mstr DDM
         ON VR.dsch_disp = DDM.dsch_disp

    -- FILTER(S)
    WHERE PAV.Dsch_Date >= @SD 
    AND PAV.Dsch_Date < @ED
    AND PAV.Plm_Pt_Acct_Type = 'I'
    AND PAV.PtNo_Num < '20000000'
    AND PDV.src_spclty_cd = 'HOSIM'
    AND PD.orgz_cd = 'S0X0'
    AND PD.pyr_name != '?'
) A

SELECT * FROM @T1

Do you think I should just create variables like @ADM_DR and @ATN_DR and in the WHERE clause set them equal to a select statement that would get them? Thank you,

Answer 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.