CODEWITHSUNDEEP
sql
user6287796
user6287796

Reputation: 21

How do I write a query to pull data from a table where linked table value is NULL or not exists

I need to write a query to pull claims from members that do not appear in the double_coverage table.

In one table I have claims data - CLAIMS Table In the second table I have member details with other coverage called double_coverage table.

I want to pull all claims except for members that appear on the second table.

The final result should only for subscriber id 

 1000007, 1000008   & 1000009

I have tried NOT IN but it's not working. Please help.

The SQL I am trying is:

 SELECT A.claim_id,
       A.subscriber_id,
       A.first_service_date dos,
       A.Payer_ID,
       DC.INSURANCE_ID,
       DC.EFFECTIVE_DATE,
       DC.STATUS,
  FROM CLAIMS A,
   (SELECT SUBSCRIBER_ID,
               EFFECTIVE_DATE,
               INSURANCE_ID, PAYER_ID
               FROM double_coverage ) DC
WHERE  DC.SUBSCRIBER_ID = A.SUBSCRIBER_ID
       AND DC.PAYER_ID = A.PAYER_ID
       AND A.SUBSCRIBER_ID NOT IN (select SUBSCRIBER_ID from double_coverage)

DOUBLE_COVERAGE TABLECLAIMS TABLE

Upvotes: 1

Views: 182

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

If you want things that don't exist, just use not in or not exists:

SELECT c.*
FROM CLAIMS c
WHERE NOT EXISTS (SELECT 1
                  FROM double_coverage dc
                  WHERE dc.subscriber_id = c.subscriber_id AND
                        dc.PAYER_ID = c.PAYER_ID
                 );

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

left join on the claims table and see which id's don't exist with a where condition.

select c.claim_id,
       c.subscriber_id,
       c.first_service_date dos,
       c.Payer_ID
from claims c
left join double_coverage dc on dc.subscriber_id = c.subscriber_id
where dc.subscriber_id is null

Upvotes: 1

Related Questions