PAYER_ID has both atributes

Hello expert,

Payers can be type Lab = 17006 and type Non_Injury_TPA = 17006.
Have drafted query that gives me PAYER_IDs that are both types,
copied herewith below.

By ordering by Payer_ ID it can be seen which Payers have both types
but there are 7k of them so a query is needed that lists only Payer
that have both types.

Tried using an AND clause and a couple of other attempts with no luck.

I think it may be a GROUP BY but not sure of the syntax.

Is there a query which will list only Payers that have PAYER_TYPE_CODE 17006
and 17007?

Thanks.

Allen in Dallas
 

SELECT PAYER_TYPE_ID,
  PAY.PAYER_ID,
  PAYER_NAME,
  PAYER_TYPE_CODE,
  PAY.LAST_UPDATE_USER,
  PAY.LAST_UPDATE_DATE,
  PAY.IS_DISABLED
FROM CCM.CCM_PAYER_TYPE PAT
join CCM_PAYER PAY on  PAY.PAYER_ID = pat.payer_id
where payer_type_code = 17006
or payer_type_code = 17007
order by PAY.PAYER_ID
Allen PittsBusiness analystAsked:
Who is Participating?
 
johnsoneConnect With a Mentor Senior Oracle DBACommented:
Assuming that I guess the correct table name, this should work.
SELECT payer_type_id, 
       PAY.payer_id, 
       payer_name, 
       payer_type_code, 
       PAY.last_update_user, 
       PAY.last_update_date, 
       PAY.is_disabled 
FROM   ccm.ccm_payer_type PAT 
       join ccm_payer PAY 
         ON PAY.payer_id = pat.payer_id 
WHERE  payer_type_code = 17006 
       AND EXISTS (SELECT 1 
                   FROM   ccm_payer pay2 
                   WHERE  pay.payer_id = pay2.payer_id 
                          AND pay2.payer_type_code = 17007) 
ORDER  BY PAY.payer_id 

Open in new window

If not, you will have to change the EXISTS subquery.
0
 
Allen PittsBusiness analystAuthor Commented:
Hello johnsone,

Works great. Did change

AND EXISTS (SELECT 1
                   FROM   ccm_payer_id pay2
to
AND EXISTS (SELECT 1
                   FROM   ccm_payer_type pay2

So a second WHERE condition is used to see if
 there is a payer_type_code = 17007 .
Simple and effective.
0
 
slightwv (䄆 Netminder) Commented:
0
 
johnsoneSenior Oracle DBACommented:
Sorry.  There was no table alias indicating which table it came from, had to guess.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.