SQL Left Join Returning Wrong Records

Kmcnet 686 Reputation points
2023-07-12T23:19:43.16+00:00

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,240 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Bruce (SqlWork.com) 52,486 Reputation points
    2023-07-13T01:32:20.9633333+00:00

    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

    1 person found this answer helpful.
    0 comments No comments

  2. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
    2023-07-13T01:35:37.1566667+00:00

    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.


  3. Kmcnet 686 Reputation points
    2023-07-18T11:00:32.9066667+00:00

    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
    
    0 comments No comments