Oracle SQL Query – Select Rows Without All IDs in Other Tables

oracle

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;

Best Answer

first approach for customers without account 

DELETE FROM CUSTOMER WHERE
ID NOT IN (SELECT CUSTOMER_ID FROM ACCOUNT);

the inner select gives you the list of customer_id's in the accounts table - the remaining data-sets in the customers table can be deleted after your first sentence.

based on that you build a similar select for the case without operations 

DELETE FROM CUSTOMER WHERE
(
  (ID NOT IN (SELECT CUSTOMER_ID FROM OPERATION) ) AND
  (ID NOT IN (SELECT id_account_sender FROM OPERATION) ) AND
  (ID NOT IN (SELECT id_account_receiver FROM OPERATION) )
);

both where conditions can be combined using OR

of course if you first want to run a select to review what is going to be deleted, you can replace "DELETE" with "SELECT *" in the 2 queries I posted

Related Question