How would you do a delete on this?

From: Sean Dolan <nospam_at_nowhere.com>
Date: Wed, 21 Feb 2001 17:17:36 -0700
Message-ID: <971ll8017nr@enews3.newsguy.com>

Enviro : Oracle 8.1.7 on NT 2000.

Three tables.

Category                    Escalation
---------------                -----------------
Cat_Id (PK)                Esc_Id (PK)
Comp_Id (FK)           Descr

Descr

Cat_to_Esc

---

Cat_Id (FK)
Esc_Id (FK)

I would like to delete all evidence of an Escalation where Cat_Id belongs to Comp_Id = 12345
How?

If I do a delete on the Cat_to_Esc first using :

   delete from cat_to_esc where cat_id in (select cat_id from category where comp_id = 12345)

then how would I then get the Escalation Ids in the Escalation Table? And no, I can not do a :
delete from escalation where escalation_id not in (select esc_id from cat_to_esc)

because there can be escalation Ids that exist but are not part of a category.

I am sure this is a "common" SQL question, but I have tried each and every way and can not come up with a quick way. I know I could use a PL/SQL package and fetch through a cursor deleting that way.... is that the only way?

Thanks,
Sean Received on Wed Feb 21 2001 - 18:17:36 CST