I'm a SQL novice, and usually figure things out via Google and SO, but I can't wrap my head around the SQL required for this.

My question is similar to [Delete sql rows where IDs do not have a match from another table](https://stackoverflow.com/questions/3384127/delete-sql-rows-where-ids-do-not-have-a-match-from-another-table), but in my case I have a middle table that I have to query, so here's the scenario:

We have this INSTANCES table that basically lists all the occurrences of files sent to the database, but have to join with CROSS\_REF so our reporting application knows which table to query for the report, and we just have orphaned INSTANCES rows I want to clean out. Each DETAIL table contains different fields from the other ones.

I want to delete all single records from INSTANCES if there are no records for that Instance ID in any DETAIL table. The DETAIL table got regularly cleaned of old files, but the Instance record wasn't cleaned up, so we have a lot of INSTANCE records that don't have any associated DETAIL data. The thing is, I have to select the Table Name from CROSS\_REF to know which DETAIL\_X table to look up the Instance ID.

In the below example then, since DETAIL\_1 doesn't have a record with Instance ID = 1001, I want to delete the 1001 record from INSTANCES.

**INSTANCES**

| Instance ID | Detail ID |
| --- | --- |
| 1000 | 123 |
| 1001 | 123 |
| 1002 | 234 |

**CROSS\_REF**

| Detail ID | Table Name |
| --- | --- |
| 123 | DETAIL\_1 |
| 124 | DETAIL\_2 |
| 125 | DETAIL\_3 |

**DETAIL\_1**

| Instance ID |
| --- |
| 1000 |
| 1000 |
| 2999 |