Solved

SQL Server 2008 - Query for a Left Join

Posted on 2015-01-08
23
286 Views
Last Modified: 2015-01-08
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
Comment
Question by:Amour22015
  • 10
  • 5
  • 4
  • +3
23 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
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
 
LVL 25

Expert Comment

by:SStory
Comment Utility
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
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
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
 

Author Comment

by:Amour22015
Comment Utility
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
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
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
 

Author Comment

by:Amour22015
Comment Utility
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
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 50 total points
Comment Utility
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
 

Author Comment

by:Amour22015
Comment Utility
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
 
LVL 24

Expert Comment

by:Phillip Burton
Comment Utility
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
 

Author Comment

by:Amour22015
Comment Utility
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
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Amour22015
Comment Utility
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
 

Author Comment

by:Amour22015
Comment Utility
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
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
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
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
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
 

Author Comment

by:Amour22015
Comment Utility
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
 

Author Comment

by:Amour22015
Comment Utility
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
 
LVL 68

Accepted Solution

by:
Qlemo earned 250 total points
Comment Utility
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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 200 total points
Comment Utility
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
 

Author Comment

by:Amour22015
Comment Utility
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
 
LVL 69

Expert Comment

by:ScottPletcher
Comment Utility
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
 
LVL 68

Expert Comment

by:Qlemo
Comment Utility
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
 

Author Closing Comment

by:Amour22015
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now