What is the accepted method to delete records referenced by a junction/join table?

Question

I have three tables as follows, with NO cascading relationships (I do not want this, as the database is primarily managed by NHibernate).

Invoice
(
    entity_id int not null,
    ...
)

Ticket
(
    entity_id int not null,
    ...
)

InvoiceTicket
(
    InvoiceId --> Not-null foreign key to Invoice.entity_id
    TicketId --> Not-null Foreign key to Ticket.entity_id
)

What I'm trying to do is delete the invoices, tickets, and related InvoiceTicket rows given a criteria on Ticket. But I have to do this externally to our application, hence why I'm building a SQL query to do it.

I've already deleted all the dependencies to both Invoice and Ticket, no problem. Since Invoice and Ticket are referenced by InvoiceTicket, I must delete InvoiceTicket rows first. However, if I do that, then my link to the Invoice table is broken (I can still delete the tickets of interest, but no longer the invoices).

What is the accepted method to perform this operation using SQL?

I solved the problem already by using a temporary table and filling it with the rows of interest from the InvoiceTicket table, but what are other people doing to solve this type of problem? I would imagine you could do this with a stored procedure as well, but I'm not as familiar with writing those. Is there a direct way of doing this operation through SQL queries?

Answer 1

If Invoices can validly exist without associated invoicetickets then RBarryYoung's solution is crap. It deletes every such invoice.

In that case, in order to correctly determine the set of invoices to delete, you must first query them and set them aside.