多次连接相同表

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

将上两个表做连接，出下面的报表：

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 p.pat_id,p.ins_id1,i1.Description,p.ins_id2,i2.Description,p.ins_id3,i3.Descriptionfrompatient p 
left join INSURANCE i1 on p.ins_id1=i1.ins_id 
left join INSURANCE i2 on p.ins_id2=i2.ins_id
left join INSURANCE i3 on p.ins_id3=i3.ins_id 
order by p.pat_id;

因为表patient里面有三个外键（Insurance表里的主键），所以得连接三个表（三个都是Insurance表）。