I have 3 tables. Table A contains Customer details and key is Cust_id and status as ACT or INACT. Table B contains Org details and key is Org_id and status as ACT or INACT or SUSP, and company code. Table C is join table between Table A and Table B.

One Cust_id can be associated with more than one Org_id and one Org_id can have more than one company code.

Report Output: List of customers that are ACT and not having a single ACT org_id.

Lets say Cust_id XYZ is active and attached to 3 Org_id's - O1(ACT), O2(INACT), O3(INACT) - should not be on report.
Cust_id ABC is active and attached to 3 org_id's - O1(INACT), O2(INACT), O3(INACT) - should be on the report.

How much data do you have on the tables? I have to ask this as it's easier to do a NOT IN for Cus_id but if you have millions of Cus_id then it probably won't work/take a long time.

If not much data then try this (I know it only gives cus_id but try it and let us know):

Code:

SELECT CUS_ID FROM TABLE1
WHERE CUS_ID NOT IN (
SELECT CUS_ID FROM TABLE1, TABLE3, TABLE2
WHERE CUS_ID_STATUS = 'ACT'
AND TABLE1.CUS_ID = TABLE3.CUS_ID
AND TABLE3.ORG_ID = TABLE2.ORG_ID
AND TABLE2.COMPANY_ID = 'AG'
AND TABLE2.END_DT IS NULL
AND TABLE2.STATUS IN ('ACT','SUSP'))
AND CUS_ID_STATUS = 'ACT'

Because there is no relationship.
Anand,
1. code tags missing
2. wrong Alias names referenced all over.
3. end_dt , the null condition is missing.
4. How do you ensure to block the cus_id if there is a even single stat 'ACT' per org_id?

Because there is no relationship.
Anand,
1. code tags missing
2. wrong Alias names referenced all over.
3. end_dt , the null condition is missing.
4. How do you ensure to block the cus_id if there is a even single stat 'ACT' per org_id?

Thank you Rohit. I have modified the query as below and tried to answer your questions. Please see if this will help.

1. code tags missing - Do you mean the code snippet from mainframe.
I am sorry, currently i do not have access to mainframes

2. wrong Alias names referenced all over - Thank you. I have corrected it now.

3. end_dt , the null condition is missing. - Requirement doesn't imply any filter condition based on END_DT column
It talks only about Status of the Cust_id and Org_id which is readily available in column Status of TableB. If the status has to checked up to date, then we have to modify accordingly

4. How do you ensure to block the cus_id if there is a even single stat 'ACT' per org_id? - Given below, I have tried to spilit the main query result and subquery result set
as i expected it to run. please correct if i am wrong.

Main query is expected to pull 7 rows in the result set by joining the 3 tables.
But for each row pulled from main query, sub query will be executed and "NOT EXISTS" clause check will be performed.
Ex: First 3 rows in the result has Y.Org_id as 22.
When the correlated sub query is run for this Org_id (22), it Exists since there is a row with status "ACT" in table B.
So, the "NOT EXISTS" clause fails and the row is not pulled to the final result set. Whereas "NOT EXISTS" clause passes for the 4th row and it will be reported to final output.