如何根据同一字段多个值join查询?

Question

我有一个BS1_BaseCode表，平时是根据它的BaseCodeType字段的值来确定对应的BaseCode字段的值，也就好像这个连接查询语句
left Join dbo.BS1_BaseCode on dbo.LD3_Consign.TrafficType=dbo.BS1_BaseCode.BaseCode and BaseCodeType='fyfsb'现在我是要在同一个查询里取BaseCodeType字段的二个值来进行连接，如下
left Join dbo.BS1_BaseCode on dbo.LD3_Consign.TrafficType=dbo.BS1_BaseCode.BaseCode and BaseCodeType='fyfsb'
left Join dbo.BS1_BaseCode on dbo.LD3_Consign.PassType=dbo.BS1_BaseCode.BaseCode and BaseCodeType='fhfsb'
但这样写的话SQL Server不鸟我，应该怎样写才合法

Answer 1

left Join dbo.BS1_BaseCode on dbo.LD3_Consign.PassType=dbo.BS1_BaseCode.BaseCode 
where BaseCodeType in('fyfsb','fhfsb')

Answer 2

left Join dbo.BS1_BaseCode a on dbo.LD3_Consign.TrafficType=a.BaseCode and BaseCodeType='fyfsb'
left Join dbo.BS1_BaseCode b on dbo.LD3_Consign.PassType=b.BaseCode and BaseCodeType='fhfsb'

Answer 3

left Join dbo.BS1_BaseCode on dbo.LD3_Consign.TrafficType=dbo.BS1_BaseCode.BaseCode where dbo.BS1_BaseCode.BaseCodeType='fyfsb' or dbo.BS1_BaseCode.BaseCodeType='fhfsb'

Answer 4

SELECT *,dbo.LD3_Consign.BillNo as abillno FROM dbo.LD3_Consign  left Join dbo.LD3_Cust
   on dbo.LD3_Consign.CustNo=dbo.LD3_Cust.BillNo left Join dbo.BS2_Department on dbo.LD3_Consign.CreateDeptNo=dbo.BS2_Department.DeptCode left Join dbo.LD2_Spot on dbo.LD3_Consign.StartSpot=dbo.LD2_Spot.SpotCode left Join dbo.BS1_BaseCode on dbo.LD3_Consign.TrafficType=dbo.BS1_BaseCode.BaseCode and BaseCodeType='fyfsb' left Join dbo.BS1_BaseCode on dbo.LD3_Consign.PassType=dbo.BS1_BaseCode.BaseCode and BaseCodeType='fhfsb' WHERE DocNo='$seach1'二楼的和四楼的用不了，我贴上我的完整语句，你们都加了where那我的where没办法处理了

Answer 5

同一个表做left join 连接得重命名一下，要不然它不知道跟哪个连接

Answer 6

[code=SQL]SELECT *,dbo.LD3_Consign.BillNo as abillno 
FROM dbo.LD3_Consign  
left Join dbo.LD3_Cust
   on dbo.LD3_Consign.CustNo=dbo.LD3_Cust.BillNo 
left Join dbo.BS2_Department 
on dbo.LD3_Consign.CreateDeptNo=dbo.BS2_Department.DeptCode
 left Join dbo.LD2_Spot 
on dbo.LD3_Consign.StartSpot=dbo.LD2_Spot.SpotCode 
left Join dbo.BS1_BaseCode 
on dbo.LD3_Consign.TrafficType=dbo.BS1_BaseCode.BaseCode and BaseCodeType='fyfsb' 
and dbo.LD3_Consign.PassType=dbo.BS1_BaseCode.BaseCode and BaseCodeType='fhfsb' 
WHERE DocNo='$seach1'
/code]

Answer 7

SELECT *,dbo.LD3_Consign.BillNo as abillno 
FROM dbo.LD3_Consign  
left Join dbo.LD3_Cust
   on dbo.LD3_Consign.CustNo=dbo.LD3_Cust.BillNo 
left Join dbo.BS2_Department 
on dbo.LD3_Consign.CreateDeptNo=dbo.BS2_Department.DeptCode
 left Join dbo.LD2_Spot 
on dbo.LD3_Consign.StartSpot=dbo.LD2_Spot.SpotCode 
left Join dbo.BS1_BaseCode 
on dbo.LD3_Consign.TrafficType=dbo.BS1_BaseCode.BaseCode and BaseCodeType='fyfsb' 
and dbo.LD3_Consign.PassType=dbo.BS1_BaseCode.BaseCode and BaseCodeType='fhfsb' 
WHERE DocNo='$seach1'

Answer 8

SELECT *,dbo.LD3_Consign.BillNo as abillno FROM dbo.LD3_Consign  
left Join dbo.LD3_Cust on dbo.LD3_Consign.CustNo=dbo.LD3_Cust.BillNo 
left Join dbo.BS2_Department on dbo.LD3_Consign.CreateDeptNo=dbo.BS2_Department.DeptCode 
left Join dbo.LD2_Spot on dbo.LD3_Consign.StartSpot=dbo.LD2_Spot.SpotCode 
left Join dbo.BS1_BaseCode a on dbo.LD3_Consign.TrafficType=a.BaseCode and a.BaseCodeType='fyfsb'
left Join dbo.BS1_BaseCode b on dbo.LD3_Consign.PassType=b.BaseCode and b.BaseCodeType='tbsxb'
 WHERE DocNo='0829078'我也整理一下，这个可以通过，但是查不到后面这个BaseCodeType='tbsxb'的数据

Answer 9

SELECT *,dbo.LD3_Consign.BillNo as abillno 
FROM dbo.LD3_Consign  
left Join dbo.LD3_Cust
   on dbo.LD3_Consign.CustNo=dbo.LD3_Cust.BillNo 
left Join dbo.BS2_Department 
on dbo.LD3_Consign.CreateDeptNo=dbo.BS2_Department.DeptCode
 left Join dbo.LD2_Spot 
on dbo.LD3_Consign.StartSpot=dbo.LD2_Spot.SpotCode 
left Join dbo.BS1_BaseCode 
on dbo.LD3_Consign.TrafficType=dbo.BS1_BaseCode.BaseCode 
and dbo.LD3_Consign.PassType=dbo.BS1_BaseCode.BaseCode  
WHERE DocNo='$seach1' and and BaseCodeType in ('fyfsb','fhfsb','tbsxb') 

Answer 10

上面多了个and
SELECT *,dbo.LD3_Consign.BillNo as abillno 
FROM dbo.LD3_Consign  
left Join dbo.LD3_Cust
   on dbo.LD3_Consign.CustNo=dbo.LD3_Cust.BillNo 
left Join dbo.BS2_Department 
on dbo.LD3_Consign.CreateDeptNo=dbo.BS2_Department.DeptCode
 left Join dbo.LD2_Spot 
on dbo.LD3_Consign.StartSpot=dbo.LD2_Spot.SpotCode 
left Join dbo.BS1_BaseCode 
on dbo.LD3_Consign.TrafficType=dbo.BS1_BaseCode.BaseCode 
and dbo.LD3_Consign.PassType=dbo.BS1_BaseCode.BaseCode  
WHERE DocNo='0829078' and BaseCodeType in ('fyfsb','tbsxb') 

Answer 11

SELECT *,dbo.LD3_Consign.BillNo as abillno 
FROM dbo.LD3_Consign  
left Join dbo.LD3_Cust
   on dbo.LD3_Consign.CustNo=dbo.LD3_Cust.BillNo 
left Join dbo.BS2_Department 
on dbo.LD3_Consign.CreateDeptNo=dbo.BS2_Department.DeptCode
 left Join dbo.LD2_Spot 
on dbo.LD3_Consign.StartSpot=dbo.LD2_Spot.SpotCode 
left Join dbo.BS1_BaseCode 
on dbo.LD3_Consign.TrafficType=dbo.BS1_BaseCode.BaseCode 
and dbo.LD3_Consign.PassType=dbo.BS1_BaseCode.BaseCode  
WHERE DocNo='0829078' and BaseCodeType in ('fyfsb','tbsxb') 这个没有结果，去掉and BaseCodeType in ('fyfsb','tbsxb') 就有结果
但没有fyfsb和tbsxb