Click here to Skip to main content
16,020,347 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have this query for join

SQL
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


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
Posted

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
 
Share this answer
 
Comments
sunil mali 13-Jul-13 6:58am    
where doesnt work in left join.
_Asif_ 13-Jul-13 7:08am    
you have not understood. what i meant is like this

select * --so many parameters
from UploadMaster um
left Join LoginData ld
on um.ProcessedBy=ld.Id
left Join BBMaster bb
on ld.BBId=bb.Id
WHERE UM.SCANID = 10071351010004
sunil mali 15-Jul-13 7:14am    
Oh sorry i misunderstood...
It worked. :)
Thanks a lot.
Whatever table you have ScanId referenced, you need to add that in your join condition like you did in "StatusMaster" left join condition
 
Share this answer
 
Comments
sunil mali 13-Jul-13 5:30am    
My scanId column is in UploadMaster table which is in from Clause
Thats why i wrote (and um.ScanId=10071351010004) at the end of my query

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900