asked on

SQL Server 2008 - Query for a Left Join

Hi Experts,

First I would like to say that I am new to all this.

I have this bit of T-SQL that I would like to run a query using a Left Join but would need some help with:

Select COUNT(*)
FROM CLAIM_2014_09 c WITH(NOLOCK)
            LEFT JOIN MEMBER m WITH(NOLOCK) ON c.SUBSCR_MBR_KY = m.MBR_KY
            LEFT JOIN CLAIM_PROVIDER_RELATIONSHIP scpr WITH(NOLOCK) ON scpr.CLM_KY = c.CLM_KY AND scpr.PRVDR_TYP_CD = 'SERVICING'
            LEFT JOIN CLAIM_PROVIDER_RELATIONSHIP bcpr WITH(NOLOCK) ON bcpr.CLM_KY = c.CLM_KY AND bcpr.PRVDR_TYP_CD = 'BILLING'
            LEFT JOIN PROVIDER p WITH(NOLOCK) ON p.PRVDR_KY = scpr.PRVDR_KY

Open in new window

The problem I am having is that when it comes to the "RELATIONSHIP" Table it causes a doubling of the records and I only want one as a result.

Here is my attempt to correct it:

Select COUNT(*)
FROM CLAIM_2014_09 c WITH(NOLOCK)
            LEFT JOIN MEMBER m WITH(NOLOCK) ON c.SUBSCR_MBR_KY = m.MBR_KY
            LEFT JOIN (Select CLM_KY, PRVDR_KY From(Select ROW_NUMBER() OVER(PARTITION BY CLM_KY ORDER BY CLM_PRVDR_RLSHP_KY desc) as Record_Seq,
            CLM_KY, PRVDR_KY From CLAIM_PROVIDER_RELATIONSHIP Where PRVDR_TYP_CD = 'SERVICING') T Where Record_Seq = 1)
            LEFT JOIN (Select CLM_KY,PRVDR_KY From(Select ROW_NUMBER() OVER(PARTITION BY CLM_KY ORDER BY CLM_PRVDR_RLSHP_KY desc) as Record_Seq,CLM_KY,
            PRVDR_KY From CLAIM_PROVIDER_RELATIONSHIP Where PRVDR_TYP_CD = 'BILLING') T Where Record_Seq = 1)
            LEFT JOIN PROVIDER p WITH(NOLOCK) ON p.PRVDR_KY = scpr.PRVDR_KY

Open in new window

But as you can see I would be getting syntax errors and the error is:
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'LEFT'.
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'LEFT'.
Msg 319, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

ALSO,
doesn't each Left Join results need to be in some kind of result to determine which one is the newest and give that one a record result?

Please help and thanks

Guy Hengel [angelIII / a3]

I think you should read up this article in regards to "duplicates":
https://www.experts-exchange.com/Database/Miscellaneous/A_3203-DISTINCT-vs-GROUP-BY-and-why-does-it-not-work-for-my-query.html
with that one, you should be able to implement the query simply and correctly withing a couple of minutes

SStory

Generally to determine newest you sort something by Date order DESC first. Have you tried the Distinct keyword at the start of the SELECT? Select Distinct .....

Phillip Burton

All of the WHEREs should be at the end, e.g.

Select COUNT(*)
FROM Table1 WITH(NOLOCK)
            LEFT JOIN Table Table1 ON Table1.field = Table2.field
            LEFT JOIN ...
            WHERE Table1.Field = 'HITHERE' and Table2.Field = 'HITHEREASWELL' and...

Open in new window

Personally, I would go back to your original code, and replace lines 4 and 5 with:

            LEFT JOIN CLAIM_PROVIDER_RELATIONSHIP scpr WITH(NOLOCK) ON scpr.CLM_KY = c.CLM_KY AND (scpr.PRVDR_TYP_CD = 'SERVICING' or bcpr.PRVDR_TYP_CD = 'BILLING')

Open in new window

or, as I said, put all of the WHEREs at the end

Amour22015

ASKER

Ok,

Phillip Burton I tried:

LEFT JOIN CLAIM_PROVIDER_RELATIONSHIP scpr WITH(NOLOCK) ON scpr.CLM_KY = c.CLM_KY AND (scpr.PRVDR_TYP_CD = 'SERVICING' or scpr.PRVDR_TYP_CD = 'BILLING')

Open in new window

But I received the same number of records (doubling), so that did not work?

SStory yes I tried the Select Distinct and again same (doubling) amounts.

Please help and thanks

Phillip Burton

It probably has worked - it's just that one field in one table is mentioned twice in another table.

However, what is unique that you want to count? Is there a primary key field you want to count?

If so, instead of Select COUNT(*), use

Select COUNT(DISTINCT mytable.myfield)

Amour22015

ASKER

The count is just for this post in the original it has this:

SELECT DISTINCT
                  c.CLM_KY,
                  c.[FILE_ID],
                  c.CLI_PRF_KY,
                  c.CLM_NR_ID,
                  c.SUBSCR_MBR_KY,
				  c.PATNT_MBR_KY,
				  c.GRP_NR,
				  c.CLM_CHRG_AM,
				  c.CLM_PD_AM,
				  c.CLM_PRSSD_DT,
                  m.FRST_NM AS SUBSCR_FRST_NM,
                  m.MID_NM AS SUBSCR_MID_NM,
                  m.LAST_NM AS SUBSCR_LAST_NM,
                  m.CLNT_SUBSCR_ID,
                  c.FRST_DT_OF_SRVC_DT, 
                  c.LAST_DT_OF_SRVC_DT,
                  p.ORG_NM AS PRVDR_ORG_NM,
                  CASE
                        WHEN pc.CLM_KY IS NOT NULL THEN 1
                        ELSE 0
                  END AS INV_FG,
                  GETDATE() AS PURG_DT,
                  c.INSRT_TS AS ACCENT_RCPT_DT,
                  bcpr.PRVDR_KY,  
                  scpr.PRVDR_KY,    
                  1

Open in new window

But I did not want to put all that in the post.

The result is the same amount of records as the count would be. I am just trying to make it less confusing but this is the whole query within the SP I am using:

INSERT INTO PURGE_STRIP
            (CLM_KY, [FILE_ID], CLI_PRF_KY, CLM_NR_ID, c.SUBSCR_MBR_KY,PATNT_MBR_KY,GRP_NR,
			CLM_CHRG_AM,CLM_PD_AM,CLM_PRSSD_DT,SUBSCR_FRST_NM, SUBSCR_MID_NM, SUBSCR_LAST_NM,
             CLNT_SUBSCR_ID, FRST_DT_OF_SRVC_DT, LAST_DT_OF_SRVC_DT, PRVDR_ORG_NM, INV_FG,
             PURG_DT, ACCENT_RCPT_DT, BILLG_PRVDR_KY,SERV_PRVDR_KY, HST_FG)      
            SELECT DISTINCT
                  c.CLM_KY,
                  c.[FILE_ID],
                  c.CLI_PRF_KY,
                  c.CLM_NR_ID,
                  c.SUBSCR_MBR_KY,
				  c.PATNT_MBR_KY,
				  c.GRP_NR,
				  c.CLM_CHRG_AM,
				  c.CLM_PD_AM,
				  c.CLM_PRSSD_DT,
                  m.FRST_NM AS SUBSCR_FRST_NM,
                  m.MID_NM AS SUBSCR_MID_NM,
                  m.LAST_NM AS SUBSCR_LAST_NM,
                  m.CLNT_SUBSCR_ID,
                  c.FRST_DT_OF_SRVC_DT, 
                  c.LAST_DT_OF_SRVC_DT,
                  p.ORG_NM AS PRVDR_ORG_NM,
                  CASE
                        WHEN pc.CLM_KY IS NOT NULL THEN 1
                        ELSE 0
                  END AS INV_FG,
                  GETDATE() AS PURG_DT,
                  c.INSRT_TS AS ACCENT_RCPT_DT,
                  bcpr.PRVDR_KY,  
                  scpr.PRVDR_KY,    
                  1
            FROM CLAIM_PURGE c WITH(NOLOCK)
            LEFT JOIN MEMBER m WITH(NOLOCK) ON c.SUBSCR_MBR_KY = m.MBR_KY
            LEFT JOIN CLAIM_PROVIDER_RELATIONSHIP scpr WITH(NOLOCK) ON scpr.CLM_KY = c.CLM_KY AND scpr.PRVDR_TYP_CD = 'SERVICING'
            LEFT JOIN CLAIM_PROVIDER_RELATIONSHIP bcpr WITH(NOLOCK) ON bcpr.CLM_KY = c.CLM_KY AND bcpr.PRVDR_TYP_CD = 'BILLING'
            LEFT JOIN PROVIDER p WITH(NOLOCK) ON p.PRVDR_KY = scpr.PRVDR_KY
            LEFT JOIN #POTENTIAL_CHECK pc WITH(NOLOCK) ON pc.CLM_KY = c.CLM_KY
            WHERE c.FILE_ID = @fileId

Open in new window

Please help and thanks

SOLUTION

Phillip Burton

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Amour22015

ASKER

Maybe I am not clear.

So let me say this in a different way.

This table:
CLAIM_2014_09

When I run a count query:

Select COUNT(*) 
FROM CLAIM_2014_09

Open in new window

I get: 1583953 records and that is how many records are in that table.

But when I run this query adding the Left Join in:

Select COUNT(*)
FROM CLAIM_2014_09 c WITH(NOLOCK)
            LEFT JOIN MEMBER m WITH(NOLOCK) ON c.SUBSCR_MBR_KY = m.MBR_KY
            LEFT JOIN CLAIM_PROVIDER_RELATIONSHIP scpr WITH(NOLOCK) ON scpr.CLM_KY = c.CLM_KY AND scpr.PRVDR_TYP_CD = 'SERVICING'
            LEFT JOIN CLAIM_PROVIDER_RELATIONSHIP bcpr WITH(NOLOCK) ON bcpr.CLM_KY = c.CLM_KY AND bcpr.PRVDR_TYP_CD = 'BILLING'
            LEFT JOIN PROVIDER p WITH(NOLOCK) ON p.PRVDR_KY = scpr.PRVDR_KY

Open in new window

I get: 1683779 records and that is more records than there is in the table.

That is the problem I am having.

Please help and thanks

Phillip Burton

But that's because there is either:

1. A duplicate m.MBR_KY, or
2. A duplicate scpr.CLM_KY, or
3. A duplicate bcpr.CLM_KY, or
4. A duplicate p.PRVDR_KY.

That's why you are getting more answers.

Amour22015

ASKER

Ok then I am trying to understand your new query so it would be this:

With CLAIM_2014_09 as (

SELECT DISTINCT
                  c.CLM_KY,
                  c.[FILE_ID],
                  c.CLI_PRF_KY,
                  c.CLM_NR_ID,
                  c.SUBSCR_MBR_KY,
				  c.PATNT_MBR_KY,
				  c.GRP_NR,
				  c.CLM_CHRG_AM,
				  c.CLM_PD_AM,
				  c.CLM_PRSSD_DT,
                  m.FRST_NM AS SUBSCR_FRST_NM,
                  m.MID_NM AS SUBSCR_MID_NM,
                  m.LAST_NM AS SUBSCR_LAST_NM,
                  m.CLNT_SUBSCR_ID,
                  c.FRST_DT_OF_SRVC_DT, 
                  c.LAST_DT_OF_SRVC_DT,
                  p.ORG_NM AS PRVDR_ORG_NM,
                  CASE
                        WHEN pc.CLM_KY IS NOT NULL THEN 1
                        ELSE 0
                  END AS INV_FG,
                  GETDATE() AS PURG_DT,
                  c.INSRT_TS AS ACCENT_RCPT_DT,
                  bcpr.PRVDR_KY,  
                  scpr.PRVDR_KY,    
                  1

)
Select count(*) from CLAIM_2014_09 c WITH(NOLOCK)
            LEFT JOIN MEMBER m WITH(NOLOCK) ON c.SUBSCR_MBR_KY = m.MBR_KY
            LEFT JOIN CLAIM_PROVIDER_RELATIONSHIP scpr WITH(NOLOCK) ON scpr.CLM_KY = c.CLM_KY AND (scpr.PRVDR_TYP_CD = 'SERVICING' or scpr.PRVDR_TYP_CD = 'BILLING')
            LEFT JOIN PROVIDER p WITH(NOLOCK) ON p.PRVDR_KY = scpr.PRVDR_KY

Open in new window

But I am getting this error:
could not be bound. on all field(s)

Please help and thanks

Qlemo

Obviously the data in CLAIM_PROVIDER_RELATIONSHIP are not unique for (PRVDR_TYP_CD, CLM_KY). The brute force method is

Select COUNT(*)
FROM CLAIM_2014_09 c WITH(NOLOCK)
LEFT JOIN MEMBER m WITH(NOLOCK) ON c.SUBSCR_MBR_KY = m.MBR_KY
LEFT JOIN (select distinct PRVDR_KY from CLAIM_PROVIDER_RELATIONSHIP WITH(NOLOCK) where PRVDR_TYP_CD = 'SERVICING') scpr
       ON scpr.CLM_KY = c.CLM_KY
LEFT JOIN (select distinct PRVDR_KY from CLAIM_PROVIDER_RELATIONSHIP WITH(NOLOCK) where PRVDR_TYP_CD = 'BILLING') bcpr 
       ON bcpr.CLM_KY = c.CLM_KY 
LEFT JOIN PROVIDER p WITH(NOLOCK) ON p.PRVDR_KY = scpr.PRVDR_KY

Open in new window

Amour22015

ASKER

Qlemo,

yes this is what I have been trying to say.

But I also have to keep in mind that the user wants the newest of what comes out of the table "Relationship"

also when I did the updated query I got this as a error:
Msg 207, Level 16, State 1, Line 6
Invalid column name 'CLM_KY'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'CLM_KY'.

Amour22015

ASKER

I was told to use the query within the Left Join as well like:

Servicing
Select
      CLM_KY,
      PRVDR_KY
From
      (Select
            ROW_NUMBER() OVER(PARTITION BY CLM_KY ORDER BY CLM_PRVDR_RLSHP_KY desc) as Record_Seq,
            CLM_KY,
            PRVDR_KY
      From
            CLAIM_PROVIDER_RELATIONSHIP
      Where
            PRVDR_TYP_CD = 'SERVICING')T
Where
      Record_Seq = 1









Billing
Select
      CLM_KY,
      PRVDR_KY
From
      (Select
            ROW_NUMBER() OVER(PARTITION BY CLM_KY ORDER BY CLM_PRVDR_RLSHP_KY desc) as Record_Seq,
            CLM_KY,
            PRVDR_KY
      From
            CLAIM_PROVIDER_RELATIONSHIP
      Where
            PRVDR_TYP_CD = 'BILLING')T
Where
      Record_Seq = 1

Open in new window

Please help and thanks

Qlemo

Sorry, I need to "publish" the join columns from the inner selects, of course:

Select COUNT(*)
FROM CLAIM_2014_09 c WITH(NOLOCK)
LEFT JOIN MEMBER m WITH(NOLOCK) ON c.SUBSCR_MBR_KY = m.MBR_KY
LEFT JOIN (select distinct PRVDR_KY, CLM_KY from CLAIM_PROVIDER_RELATIONSHIP WITH(NOLOCK) where PRVDR_TYP_CD = 'SERVICING') scpr
       ON scpr.CLM_KY = c.CLM_KY
LEFT JOIN (select distinct PRVDR_KY, CLM_KY from CLAIM_PROVIDER_RELATIONSHIP WITH(NOLOCK) where PRVDR_TYP_CD = 'BILLING') bcpr 
       ON bcpr.CLM_KY = c.CLM_KY 
LEFT JOIN PROVIDER p WITH(NOLOCK) ON p.PRVDR_KY = scpr.PRVDR_KY

Open in new window

Qlemo

Taking your new pieces, those should work if you replace my "(select distinct ...)" with "(select CLM_KY, PRVDR_KY from ...)" exactly as you posted in http:#a40538184.

Amour22015

ASKER

Qlemo

That just makes the execute run and run without a stop.

also keep in mind that the user want the lastest values coming out of "Relationship:" table. I think that is why there is a "as Record_Seq" within the query above?

Please help and thanks...

Amour22015

ASKER

So do you mean this:

Select COUNT(*)
FROM CLAIM_2014_09 c WITH(NOLOCK)
            LEFT JOIN MEMBER m WITH(NOLOCK) ON c.SUBSCR_MBR_KY = m.MBR_KY
            LEFT JOIN (Select CLM_KY, PRVDR_KY From(Select ROW_NUMBER() OVER(PARTITION BY CLM_KY ORDER BY CLM_PRVDR_RLSHP_KY desc) as Record_Seq,
            CLM_KY, PRVDR_KY From CLAIM_PROVIDER_RELATIONSHIP Where PRVDR_TYP_CD = 'SERVICING') T Where Record_Seq = 1)
            LEFT JOIN (Select CLM_KY,PRVDR_KY From(Select ROW_NUMBER() OVER(PARTITION BY CLM_KY ORDER BY CLM_PRVDR_RLSHP_KY desc) as Record_Seq,CLM_KY,
            PRVDR_KY From CLAIM_PROVIDER_RELATIONSHIP Where PRVDR_TYP_CD = 'BILLING') T Where Record_Seq = 1)
            LEFT JOIN PROVIDER p WITH(NOLOCK) ON p.PRVDR_KY = scpr.PRVDR_KY

Open in new window

That is what I posted in ID: 28592901 above...

And I got errors:
Msg 156, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'LEFT'.
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'LEFT'.
Msg 319, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

ASKER CERTIFIED SOLUTION

Qlemo

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Amour22015

ASKER

Qlemo,
That seems to be working at least I am now getting the correct number of records. But how does it know to take the latest record and place that information in the record?

ScottPletcher,
Does that mean I may have problems later? and I should use what you mentioned?

Still confused on how it would take the latest even with the Select Top (1) I know that it takes the latest at that point being the top 1 but the query mentions that twice?

Thanks

Scott Pletcher

Qlemo's query will work, just less efficiently.

There are two "Top (1)" because you have to do two different lookups:
one for 'SERVICING' and
one for 'BILLING'

Qlemo

The subselects you provided select the first row for all CLM_KY, so we have a lot of keys with one match each. Then we just join the specific CLM_KY. I intentionally did not change the formatting to make that visible.
Whether you need the left joins depends on your data. If there is always a relationship defined, a inner join is better. If there might be relations missing, and you still want to get results, you need the outer join.

Amour22015

ASKER

Thank you both for helping me. I had to give some points too: Phillip Burton after all he tried to help. Thanks all