SQL to build a list using either EXIST (or NOT EXIST) or a combination of both

Question

I want to find the list of COMPANYID/USERID combos on the USER table that have at least 1 Account found on the ACCOUNT table, but all Accounts are closed. (If there are no Accounts (on ACCOUNT) for the CompanyID/UserID (on USER), it should not be in the results). If just one of the Accounts for a CompanyID/UserID (on USER) is OPEN (on the ACCOUNT table), the CompanyID/UserID should not be on the list.

In the example below, ABC/USER3 is the only combo that fits this. It has 2 accounts (333 & 666) and both are closed. The rest of the CompanyID/UserID combinations have least 1 ACCOUNT that is OPEN. DDD/USER5 is excluded because 888 is not on the ACCOUNT table.

TABLE: USERS

COMPANYID	USERID	ACCOUNT
AAA	USER1	111
AAA	USER1	333
ABB	USER2	333
ABB	USER2	444
ABC	USER3	333
ABC	USER3	666
BBB	USER4	555
CCC	USER4	666
DDD	USER5	888

TABLE: ACCOUNT

ACCOUNT	STATUS
111	OPEN
222	OPEN
333	CLOSED
444	OPEN
555	OPEN
666	CLOSED
777	OPEN

I wrote the original question with table names that I thought would make it easier to follow (instead of the actual DB2 tables I am using) NB002.NBTBAUTH (is USERS) NB002.NBTBMF (is ACCOUNT)

This is the SQL I tried to use:

SELECT NB002.NBTBAUTH.*, Q.USERID, Q.CNTOPEN
FROM NB002.NBTBAUTH LEFT JOIN (SELECT NB002.NBTBAUTH.USERID,
COUNT(IF(NB002.NBTBMF.ACCOUNTSTATUS = 'OPEN',1,NULL)) AS CNTOPEN 
FROM USERS INNER JOIN NB002.NBTBMF ON NB002.NBTBAUTH.ACCOUNTID
     = NB002.NBTBMF.ACCOUNTID
GROUP BY USERID) AS Q ON NB002.NBTBAUTH.USERID = Q.USERID
WHERE NOT Q.USERID IS NULL AND Q.CNTOPEN = 0;

Error message:

SQLCODE -104 Illegal Symbol "=".

It is the first = that is causing it. I changed it to a < and got the same message for the <.

Answer 1

/*
WITH 
  USERS (COMPANYID, USERID, ACCOUNT) AS
(
          SELECT 'AAA', 'USER1', '111' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'AAA', 'USER1', '333' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'ABB', 'USER2', '333' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'ABB', 'USER2', '444' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'ABC', 'USER3', '333' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'ABC', 'USER3', '666' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'BBB', 'USER4', '555' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'CCC', 'USER4', '666' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT 'DDD', 'USER5', '888' FROM SYSIBM.SYSDUMMY1
)
, ACCOUNTS (ACCOUNT, STATUS) AS
(
          SELECT '111', 'OPEN'   FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT '222', 'OPEN'   FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT '333', 'CLOSED' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT '444', 'OPEN'   FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT '555', 'OPEN'   FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT '666', 'CLOSED' FROM SYSIBM.SYSDUMMY1
UNION ALL SELECT '777', 'OPEN'   FROM SYSIBM.SYSDUMMY1
)
*/
SELECT U.USERID
FROM USERS U
JOIN ACCOUNTS A ON A.ACCOUNT = U.ACCOUNT
GROUP BY U.USERID
HAVING COUNT (1) = COUNT (CASE A.STATUS WHEN 'CLOSED' THEN 1 END)

USERID
USER3

It's not clear what the USER identifier is - if it's USERID or (COMPANYID, USERID). If latter, then add U.COMPANYID into GROUP BY and SELECT.