left join应用

Patient				Insurance
Pat_ID	Ins_ID_1	Ins_ID_2	Ins_ID_3	Ins_ID	Description
1	5			1	Medicare
2	8			2	Blue Cross
3	4	12		3	OXFORD
4	11			4	1st Health Ins
5	10	7	1	5	United Healthcare
6	5	7		6	Travellers
7	3	7	2	7	Medicaid
8	4	9	5	8	Capital Healthplan
9	3			9	MVP Healthcare
10	1			10	Harvard Healthplan

 

用Join的方式来产生如下的报表，每个Ins ID后面加上相应的描述。如InsID不能在Insurance 表中发现，即用空格表示：（如ID 11 和12）

Pat_ID	InsID1	Desc-1	InsID2	Desc-2	InsID3	Desc-3
1	5	United Healthcare
2	8	Capital Healthplan
3	4	1st Health Ins	12
4	11
5	10	Harvard Healthplan	7	Medicaid	1	Medicare
6	5	United Healthcare	7	Medicaid
7	3	OXFORD	7	Medicaid	2	Blue Cross
8	4	1st Health Ins	9	MVP Healthcare	5	United Healthcare
9	3	OXFORD
10	1	Medicare

 

代码：

Select pat_id,P.INS_ID_1,T.DESCRIPTION AS DE1,P.INS_ID_2,T1.DESCRIPTION AS DE2,P.INS_ID_3,T2.DESCRIPTION AS DE3
FROM PATIENT P  LEFT   JOIN INSURANCE T ON
P.INS_ID_1=T.INS_ID
LEFT JOIN INSURANCE T1 ON  P.INS_ID_2=T1.INS_ID
LEFT JOIN INSURANCE T2 ON P.INS_ID_3=T2.INS_ID
ORDER BY PAT_ID;

LEFT JOIN就是不管右边的表中有没有此列，只要左边的表中有就进行匹配或者直接出结果。