0

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;
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.