连接具有两列的两个表SQL Server 2008 R2 [英] 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 desired information looks like this
VISIT ID | ATN_DR_NO | ADM_DR_NO |...
12345678 | 987654 | 123456 |...
其中ATN_DR_NO是主治医生ID号，而ADM_DR_NO是主治医生ID.我有第二张桌子，上面有医生ID号和它们的相应名称，就像这样
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
您认为我应该只创建像@ADM_DR和@ATN_DR这样的变量，并在WHERE子句中将它们设置为等于将获取它们的select语句吗?
谢谢
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,
推荐答案
您再次加入该表，因此好像您是两次从同一张表中复制一张(一次用于主治医生查询，一次用于主治医生查询)医生查找.)
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>
，并且您的内部连接针对第一个医生，而a2的连接针对第二个医生.
and your inner join for a targets the first doctor, the join for a2 targets the second doctor.
请原谅伪代码，但我想您已经明白了.您会注意到，a和a2都从Doctors表中获取了doc_name字段，但是它们与b表中的不同ID相连.
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.
这篇关于连接具有两列的两个表SQL Server 2008 R2的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
