Mysql – Fetching Data from joining table for any one from condition satisfied

MySQL

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

Best Answer

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')
)
Related Question