• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • Last Modified:

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
0
Amour22015
Asked:
Amour22015
  • 10
  • 5
  • 4
  • +3
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I think you should read up this article in regards to "duplicates":
http://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
0
 
SStoryCommented:
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 .....
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Amour22015Author Commented:
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
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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)
0
 
Amour22015Author Commented:
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
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
This is the number of records you want to count?

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


In which case, start with

With myTable as (

Open in new window


and end with

)
Select count(*) from myTable

Open in new window


Making this:

With myTable 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 myTable

Open in new window

0
 
Amour22015Author Commented:
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
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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.
0
 
Amour22015Author Commented:
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
0
 
QlemoC++ DeveloperCommented:
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

0
 
Amour22015Author Commented:
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'.
0
 
Amour22015Author Commented:
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
0
 
QlemoC++ DeveloperCommented:
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

0
 
QlemoC++ DeveloperCommented:
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.
0
 
Amour22015Author Commented:
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...
0
 
Amour22015Author Commented:
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.
0
 
QlemoC++ DeveloperCommented:
Sigh. If you use FROM (select ...) in MSSQL, you need to provide an alias after the closing bracket to identify that result (and all results in the select need to be named if an expression - no issue here).
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
) scpr
       ON scpr.CLM_KY = c.CLM_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 = 'BILLING')T
  Where
      Record_Seq = 1
) 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

0
 
Scott PletcherSenior DBACommented:
You don't need ROW_NUMBER() or (obscure) LEFT JOINs -- in fact, you especially don't want LEFT JOINs, as that will still leave you with the "extra rows" problem!

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,
                 (SELECT TOP (1) PRVDR_KY
                   FROM CLAIM_PROVIDER_RELATIONSHIP scpr WITH(NOLOCK)
                   WHERE scpr.CLM_KY = c.CLM_KY AND scpr.PRVDR_TYP_CD = 'SERVICING'
                   ORDER BY CLM_PRVDR_RLSHP_KY DESC) AS PRVDR_KY,
                  (SELECT TOP (1) PRVDR_KY
                   FROM CLAIM_PROVIDER_RELATIONSHIP bcpr WITH(NOLOCK)
                   WHERE bcpr.CLM_KY = c.CLM_KY AND bcpr.PRVDR_TYP_CD = 'BILLING'
                   ORDER BY CLM_PRVDR_RLSHP_KY DESC) AS PRVDR_KY,
                 1
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' --DELETED!!
            --LEFT JOIN CLAIM_PROVIDER_RELATIONSHIP bcpr WITH(NOLOCK) ON bcpr.CLM_KY = c.CLM_KY AND bcpr.PRVDR_TYP_CD = 'BILLING' --DELETED!!

            LEFT JOIN PROVIDER p WITH(NOLOCK) ON p.PRVDR_KY = scpr.PRVDR_KY
0
 
Amour22015Author Commented:
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
0
 
Scott PletcherSenior DBACommented:
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'
0
 
QlemoC++ DeveloperCommented:
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.
0
 
Amour22015Author Commented:
Thank you both for helping me.  I had to give some points too: Phillip Burton after all he tried to help.  Thanks all
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 10
  • 5
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now