Recursive sql subset query, using connect by

Question

I have two tables that look a little like this

AGR_TERR_DEF_TERRS
  ID
  DEF_ID
  TERRITORY_ID (foreign key links to TERRITORIES.ID)

TERRITORIES
  ID
  NAME
  PARENT_ID
(parent_id and id are recursive)

Given two DEF_IDs, I need a function which checks whether the territories of one is a complete subset of the other. I've been playing with CONNECT BY, and INTERSECT, but have written a big mess rather than a useful function.

I'm hoping there will be a (relatively) easy SQL query that works.

Answer 1

Building on @Tony Andrews answer this would produce zero rows when the territories implied by def_id_1 are a subset of those for def_id_2,

select id from territories start with id in
  (select territory_id from agr_terr_def_terrs where def_id = :def_id_1)
  connect by parent_id = prior id
minus
select id from territories start with id in
  (select territory_id from agr_terr_def_terrs where def_id = :def_id_2)
  connect by parent_id = prior id

Answer 2

Given the query to get all the terrories for a given DEF_ID (I'm not quite sure what that is with your tables), DEF_ID A is a subset of DEF_ID B if the following query returns no rows:

select statement for A
MINUS
select statement for B

Does that help?