I need to delete customers if customer not have any accounts or all of accounts not have any operations (no records in id_customer, id_account_sender, id_account_receiver in operation table).
example of insert to tables:
INSERT INTO CUSTOMER (ID, SECOND_NAME, FIRST_NAME, MIDDLE_NAME, BIRTHDATE, PASSPORT, PHONE_NUMBER, ID_DISCOUNT, ADDRESS) VALUES (141, 'w', 'w', 'w', '15-APR-89', '521522221', '(847) 256-4120', null, 'new 4'); INSERT INTO ACCOUNT (ID, ID_CUSTOMER, DATE_OPEN, ID_DEPARTMENT) VALUES (121, 140, TO_DATE('2019-11-12 23:41:12', 'YYYY-MM-DD HH24:MI:SS'), 4); INSERT INTO OPERATION (ID, ID_CUSTOMER, DATE_OPERATION, TYPE, AMOUNT_TRANSFER, ID_ACCOUNT_SENDER, ID_ACCOUNT_RECEIVER) VALUES (131, 5, TO_DATE('2019-11-12 23:31:56', 'YYYY-MM-DD HH24:MI:SS'), 22, 15, 9, 8);
some queries that I tried: all accounts without operations, but I don't know how select all customers that only contain only such accounts or no accounts even.
SELECT A1.ID, C1.ID FROM ACCOUNT A1 JOIN CUSTOMER C1 ON C1.ID = A1.ID_CUSTOMER WHERE (A1.ID NOT IN (SELECT O1.ID_ACCOUNT_SENDER FROM OPERATION O1)) AND (A1.ID NOT IN (SELECT O1.ID_ACCOUNT_RECEIVER FROM OPERATION O1)) ORDER BY A1.ID;
I try following query, but it give me incorrect result:
SELECT C1.ID FROM CUSTOMER C1 FULL JOIN ACCOUNT A1 ON A1.ID_CUSTOMER = C1.ID WHERE A1.ID NOT IN (SELECT A1.ID FROM ACCOUNT A1 WHERE A1.ID NOT IN (SELECT O1.ID_ACCOUNT_SENDER FROM OPERATION O1)) AND (A1.ID NOT IN (SELECT O1.ID_ACCOUNT_RECEIVER FROM OPERATION O1)) ORDER BY A1.ID;