Recursive query of a subset of sql using

Question

I have two tables that look a bit 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 the two DEF_IDs, I need a function that checks if the territory of one of them is a complete subset of the other. I played with CONNECT BY and INTERSECT, but wrote a big mess, not a useful feature.

I hope there will be a (relatively) simple SQL query that works.

Answer 1

Based on @Tony Andrews, the answer to this will result in null strings if the territories implied by def_id_1 are subsets of 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

DEF_ID ( , ), DEF_ID A DEF_ID B, :

select statement for A
MINUS
select statement for B

?