CODEWITHSUNDEEP
sqlsqlite
user3281970
user3281970

Reputation: 3

SQLite - Delete rows based on 2 other tables

My invoicing software takes a long time to launch so I'm trying to clear out the old data (anything not related to my current 'BusinessID') but I'm stuck!

I have 3 tables - I've listed the relevant columns for each table:

companies (clients): ID, clientOfBusinessID

invoices: ID, invoiceOfClientID

invoice_line_items lineItemOfInvoiceID,

I would like to delete all *invoice_line_items* rows that ARE NOT related to my current business.

The following statement seems to show me all the clients that are not related to my current business:

companies.clientOfBusinessID NOT LIKE '%41A1%'

How would I go about this? I've had a play with JOIN queries but can't quite get it together.

To be honest I have no idea what I'm doing, just reading guides online and this is the best I could come up with - doesn't work at all!

    DELETE from invoice_line_items
    FROM invoices JOIN companies ON invoices.invoiceOfClientID = companies.id
    JOIN invoice_line_items ON invoices.id = invoice_line_items.lineItemOfInvoiceID
    WHERE companies.clientOfBusinessID NOT LIKE '%41A1%'

Any ideas?

Thanks a lot

Upvotes: 0

Views: 126

Answers (1)

Brendan
Brendan

Reputation: 1247

This query (if I understand your structire correctly) will give you all invoices that are not ralated to your company:

SELECT i.*
FROM invoices i
INNER JOIN companies c
  ON i.invoiceOfClientID = c.id
WHERE c.clientOfBusinessID NOT LIKE '%41A1%'

To delete the Invoice Line Items we can use the query above as follows:

DELETE from invoice_line_items
WHERE lineItemOfInvoiceID IN 
  (SELECT i.id
   FROM invoices i
   INNER JOIN companies c
     ON i.invoiceOfClientID = c.id
   WHERE c.clientOfBusinessID NOT LIKE '%41A1%')

You might then also want to delete the invoices too:

DELETE from invoices
WHERE id IN 
  (SELECT i.id
   FROM invoices i
   INNER JOIN companies c
     ON i.invoiceOfClientID = c.id
   WHERE c.clientOfBusinessID NOT LIKE '%41A1%')

Upvotes: 1

Related Questions