I have a task set which requires the rooting and removal of demo accounts in a database. I have created a query where all the demo accounts are returned but there are fields within this query that have various children/keys. I now want to create a query where I collect all the demo accounts as well as return all entries where the dependencies are returned so a delete statement can be created and run to remove any entries that relate to these accounts.
What is the best method of retrieving all this data in one place so a delete statement can run off minimal code?
Here is my statement for finding the Demo accounts;
Select ct.id as 'cutomer id', c.id as 'client id', ct.name, da.demo_account_status, da.demo_expiry_date, c.enabled from client c
join customer ct on ct.client_id = c.id
join demo_account da on da.client_id = c.id
join demo_account_extend_survey das on das.demo_account_id = da.id
where da.demo_account_status = 'DEMO_ACCOUNT_EXPIRED'
and c.enabled = 0
group by ct.id;
Example data of the below looks like this;
233477 26412 customer name DEMO_ACCOUNT_EXPIRED 2016-11-10 0
233478 26412 customer name DEMO_ACCOUNT_EXPIRED 2016-11-10 0
233479 26412 customer name DEMO_ACCOUNT_EXPIRED 2016-11-10 0
233480 26412 customer name DEMO_ACCOUNT_EXPIRED 2016-11-10 0
And the this attempted statement is for gathering all foreign key/children ID's in one place so that a delete can be done at a later stage.
select ct.id, p.id, c.id, cc.id, ca.id, si.id, isi.id, ct.billing_address_id from customer ct
join client c on c.id=ct.client_id
join person p on p.id = c.id
join customer_contact cc on cc.customer_id = ct.id
join customer_account ca on ca.customer_id = ct.id
join sales_invoice si on si.customer_id = ct.id
join issued_customer_credit_note_tx isi on isi.customer_id = ct.id
join address ad on ad.id = ct.billing_address_id
where c.id =
