Sql左连接问题与查询 [英] Sql Left Join Issue with query

问题描述

我有这个加入查询

I have this query for join

select um.DOCRcvdDt as InwardDate, um.ScanId,um.ProposerName as CustomerName,um.IntermediaryCode,um.ECNNumber,um.ProposalAmount,um.dtTime,um.Remarks,bb.BBName as Branch,i.NAME,
	it.[Description] as PaymentMode,r.InstrumentNumber,r.InstrumentDate,r.Amount as InstrumentAmount,a1.dtTime QC1PickedDtTime,ld1.Username as QC1UserId,a2.dtTime QC1DoneDtTime,
	a3.dtTime DEPickedDtTime,a4.dtTime DEDoneDtTime,a5.dtTime QC2PickedDtTime,a6.dtTime QC2DoneDtTime,ld2.Username as DEUserId,ld3.Username as QC2UserId,
	a7.dtTime as PolicyIssuedDtTime,sm.ContactId,sm.ProposalNo,sm.PolicyNo,sm.ReceiptNo,ld4.Username as ScanUserId
	from UploadMaster um
	left Join LoginData ld
	on um.ProcessedBy=ld.Id 
	left Join BBMaster bb
	on ld.BBId=bb.Id
	left Join IMD i
	on i.[IMD code]=um.IntermediaryCode
	left Join T_Instrument_Type it
	on it.ID=um.PaymentMode
	left Join Receipt r
	on r.TxnId=um.ScanId
	left Join Aging a1
    on a1.UpldMstrId=um.Id and a1.Status='QC1 Picked'
	left Join LoginData ld1
	on ld1.Id=a1.UpdatedBy
	left Join Aging a2
    on a2.UpldMstrId=um.Id and a2.Status='QC1 Accepted'
	left Join Aging a3
    on a3.UpldMstrId=um.Id and a3.Status='DE Picked'
	left Join LoginData ld2
	on ld2.Id=a3.UpdatedBy
	left Join Aging a4
    on a4.UpldMstrId=um.Id and a4.Status='DE Accepted'
	left Join Aging a5
    on a5.UpldMstrId=um.Id and a5.Status='DE Picked'
	left Join LoginData ld3
	on ld3.Id=a5.UpdatedBy
	left Join Aging a6
    on a6.UpldMstrId=um.Id and a6.Status='DE Accepted'
	left Join Aging a7
	on a7.Status='Policy Issued' and a7.UpldMstrId=um.Id
	left Join StatusMaster sm
	on sm.TransactionId=um.ScanId
	left Join LoginData ld4
	on ld4.Id=um.EmpId

它给了我



4行



如果我放一个过滤器

ex-



和um.ScanId = 10071351010004





它还给我4行ins一个...在这种情况下，scanid是独一无二的



请帮助



问候

SUNIL

It gives me

4 rows

if i put one filter
ex-

and um.ScanId=10071351010004


its still giving me 4 rows insted of one... in this case scanid is unique

Please help

Regards
SUNIL

推荐答案

调试这个sql的最好方法是注释所有左连接并在查询末尾添加这样的where条件



WHERE UM.SCANID = 10071351010004



一旦你得到了想要的结果然后你一个接一个转在你的左边连接。通过这种方式，您可以更好地调试并快速找到问题并快速解决问题

The best way to debug this sql is to comment all the left joins and add a where condition like this at the end of the query

WHERE UM.SCANID = 10071351010004

Once you get the desired result then one by one you turn on your left joins. This way you can better debug and quickly find the issue and resolve it quickly as well

无论您使用ScanId引用哪个表，您都需要像在StatusMaster中那样在连接条件中添加它左连接条件

Whatever table you have ScanId referenced, you need to add that in your join condition like you did in "StatusMaster" left join condition

这篇关于Sql左连接问题与查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！