I've three tables the following (please forgive the loose syntax), without any cascading down associations (I don't want this, because the database is mainly handled by NHibernate).

Invoice

(

    entity_id int not null,

    ...

)

Ticket

(

    entity_id int not null,

    ...

)

InvoiceTicket

(

    InvoiceId --> Not-null foreign answer to Invoice.entity_id

    TicketId --> Not-null Foreign answer to Ticket.entity_id

)

What I am attempting to do is remove the bills, tickets, and related InvoiceTicket rows given a criteria on Ticket. But I must do that externally to the application, hence why I am creating a SQL query to get it done.

I have already erased all of the dependencies to both Invoice and Ticket, not a problem. Since Invoice and Ticket are recommended by InvoiceTicket, I have to remove InvoiceTicket rows first. However, basically do this, then my connect to the Invoice table is damaged (I'm able to still remove the tickets of great interest, but no more the bills).

What's the recognized approach to perform this operation using SQL?

I reduced the problem already using a temporary table and filling it using the rows of great interest in the InvoiceTicket table, but what exactly are others doing to resolve this kind of problem? I'd imagine you could do this this having a saved procedure too, but I am less acquainted with writing individuals. It is possible to direct method of carrying this out operation through SQL queries?

Well, here's the way i would get it done:

BEGIN TRANSACTION
    DELETE FROM InvoiceTicket
    WHERE EXISTS(
        SELECT *
        FROM TICKET t
        WHERE {t.* conditions are met}
        )

    DELETE FROM Ticket
    WHERE {t.* conditions are met}

    DELETE FROM Invoice
    WHERE NOT EXISTS(
        SELECT *
        FROM InvoiceTicket it
        WHERE Invoice.entity_id = InvoiceTicket.InvoiceId
        )
COMMIT TRANSACTION

It's been validly stated this approach (above) only works if Bills require a minumum of one connected ticket. Although this is true, additionally, it boosts the converse question, that is you may not wish to Remove every invoice connected together with your matching tickets? Simply because they can also be connected along with other, non-erased tickets too.

You can place the rows from InvoiceTicket right into a temporary table, after which remove InvoiceTicket, Ticket and lastly Invoice in the ids within the temporary table. Finally blow away the temporary table.

I'm not sure if that's based on all DBMS, however in mySQL, you are able to remove from the JOIN around the tables. Something similar to:

DELETE Invoice, Ticket, InvoiceTicket
FROM Invoice, InvoiceTicket, Ticket
WHERE (condition on Ticket) 
  AND Ticket.Id = InvoiceTicket.TicketId 
  AND InvoiceTicket.InvoiceId = Invoice.Id

If Bills can validly exist without connected invoicetickets then RBarryYoung's option would be garbage. It removes every such invoice.

For the reason that situation, to be able to properly determine the group of bills to remove, you have to first query them and hang them aside.

It's been validly stated that removing the bills themselves might produce problems if individuals bills are recommended by invoicetickets which are themselves not erased.

Although true, I wish to explain that nowhere have I designed to suggest (and nowhere have I really stated) the group of bills-to-be-erased ought to be equal towards the group of bills whose id are available in any invoiceticket-to-be-erased.

If anybody construed my message to imply that, I must warn individuals people from the risks of making presumptions too easily and too eagerly.