SQL Left Join Returning Wrong Records

Question

Hello everyone and thanks for the help in advance. I am having problems with a SQL statement that should be fairly simple, but doesn't seem to work correctly. The statement should be a Left Join that returns all of the records form table one and matches them to records in table two.

DECLARE @Insurance		varchar(500)
DECLARE @CPTCode		varchar(50)

SET @Insurance = 'Company Name'
SET @CPTCode = '99395'


Select * from tbl_Log_ClaimDetail
Where tbl_Log_ClaimDetail.CPTCode = @CPTCode And tbl_Log_ClaimDetail.TransInsuranceCode = @Insurance and tbl_Log_ClaimDetail.TransType = 2

returns three distinct records. However

Select * from tbl_Log_ClaimDetail Left JOIN tbl_Log_ExpectedPayments ON tbl_Log_ClaimDetail.CPTCode = tbl_Log_ExpectedPayments.CPTCode Where tbl_Log_ClaimDetail.CPTCode = @CPTCode And tbl_Log_ClaimDetail.TransInsuranceCode = @Insurance and tbl_Log_ClaimDetail.TransType = 2

returns 15 records which is all the records from tbl_Log_ExpectedPayments with a CPTCode of 99395. The records from tbl_Log_ClaimDetail repeat themselves. What am I doing wrong?

Answer 1

that is how joins work. its the product of the two tables, then filtered by the where cause. without a where cause if you join table a and b, for every row in a, get every row in b.

create table #a (value int)
create table #b (value int)

insert #a values(1)
insert #a values(2)
insert #a values(3)
insert #b values(4)
insert #b values(5)
insert #b values(6)

select *
from #a,#b
order by 1,2
>>>
value	value
1	    4
1	    5
1	    6
2	    4
2	    5
2	    6
3	    4
3	    5
3	    6

note: an on clause is really a where

Answer 2

Hi @Kmcnet

Since you did not provide a table creation statement, just based on your query, I made some modifications, you can try them.

Select * from tbl_Log_ClaimDetail as A Left JOIN tbl_Log_ExpectedPayments as B 
ON A.CPTCode = B.CPTCode
Where A.CPTCode = @CPTCode 
And A.TransInsuranceCode = @Insurance 
and A.TransType = 2;

Or:

;with CTE as(
  Select * from tbl_Log_ClaimDetail
  Where CPTCode = @CPTCode And TransInsuranceCode = @Insurance and TransType = 2)
select * from CTE as A left join tbl_Log_ExpectedPayments as B
on A.CPTCode = B.CPTCode;

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

So after a lot of trial and error, I solved the problem using a compound JOIN

Select * from tbl_Log_ClaimDetail
Left JOIN tbl_Log_ExpectedPayments
ON (tbl_Log_ClaimDetail.CPTCode = tbl_Log_ExpectedPayments.CPTCode AND tbl_Log_ClaimDetail.TransInsuranceCode = tbl_Log_ExpectedPayments.insurance)
Where tbl_Log_ClaimDetail.CPTCode = @CPTCode And tbl_Log_ClaimDetail.TransInsuranceCode = @Insurance and tbl_Log_ClaimDetail.TransType = 2