0

I have two table called Activity And Claim. Activity Table contains Activity id and all other Activity Details. Claim Table contains all claim related information for the Activity. One activity have multiple claims and activity id is the foreign key for claim table.

In claim table i am maintaining different status(Approved, Deleted, rejected, Submitted) for each claim.

Currently i am facing one issue for fetching data from activity table if any one of the claim present in claim table and any one of the claim status not in Approved, rejected, submitted.

For an example if an Activity have two different claim and one claim is approved status and another claim in deleted status. So in my sql query should returns empty(because one claim already approved for activity). In another example if the activity have only one claim and status is deleted. This cause my query should fetch the Activity Details(because the activity have only one claim and it's on deleted status).

The query which i am currently using is below

SELECT * 
    FROM myactivities_4 
    LEFT JOIN claims2 ON claims2.activityid = myactivities_4.activityid 
    and claim_status NOT IN ('Submitted', 'Approved','Rejected')

Could any one help me to resolve this issue

0

Try it:

SELECT * 
FROM myactivities_4 
LEFT JOIN claims2 ON claims2.activityid = myactivities_4.activityid 
WHERE myactivities_4.activityid NOT IN (
    SELECT activityid  
    FROM claims2 
    WHERE claim_status NOT IN ('Submitted', 'Approved','Rejected')
)
1
  • Don't duplicate the test on claim_status. When using LEFT JOIN, the two places give different results.
    – Rick James
    Apr 21 '18 at 5:19
0
SELECT * 
    FROM myactivities_4 
    WHERE NOT EXISTS ( SELECT 1 FROM claims2
            WHERE activityid = myactivities_4.activityid 
              AND claim_status IN ('Submitted', 'Approved','Rejected') )

(If this does not do what you desire, please provide some sample data that exemplifies what you desire.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.