How to query from a hierarchy in oracle

Question

I work with an Oracle database that has tables for customers, chains and campaigns. Like so (stripped down):

TABLE CUSTOMER 
  ACCOUNTNUMBER   VARCHAR2
  CHAIN           VARCHAR2
  CATEGORY        VARCHAR2

TABLE CHAIN
  CODE            VARCHAR2
  PARENTCHAIN     VARCHAR2

TABLE CAMPAIGN
  ID              NUMBER
  DISCOUNT        NUMBER

TABLE CAMPAIGN_ELIGIBILITY
  CAMPAIGNID      NUMBER   --ID of the campaign
  ACCOUNTNUMBER   VARCHAR2 --Customer included in the campaign
  CHAINCODE       VARCHAR2 --Chain included in the campaign
  CUSTCATCODE     VARCHAR2 --Customercategory included in the campaign

A customer can be eligible for a campaign specifically (based on accountnumber), through his defined customergroup or as a member of a chain.

I use this query to find all campaigns that are valid for a given customer:

select * from campaign where id in
(
  select unique campaignid from campaign_eligibility where 
    accountnumber=:accountnumber
  union
  select unique campaignid from campaign_eligibility where 
    chaincode = 
      (select chain from debtable where accountnumber=:accountnumber)
  union
  select unique campaignid from campaign_eligibility where 
    custcatcode =
      (select category from customer where accountnumber=:accountnumber)
) 

Now, let's say that the customer "Popeye Spinach Empire" is a member of the chain "Spinach Dealers Inc.", which in turn is part of the larger chain "GreenFud R US", which in turn is part of the chain "FoAC". When there's a campaign that targets "GreenFud R US", Popeye is eligible for that campaign. My query will only return campaigns where Popeye has been specifically added, or that targets "Spinach Dealers Inc."

How can I modify my query to include campaigns that a customer is indirectly part of?

Answer 1

WITH    chains AS
        (
        SELECT  code
        FROM    chain c
        START WITH
                c.code IN
                (
                SELECT  chain
                FROM    campaign_eligibility
                WHERE   accountnumber = :acc
                UNION ALL
                SELECT  chain
                FROM    customer c
                JOIN    campaign_eligibility ce
                ON      ce.custcatcode = c.category
                WHERE   accountnumber = :acc
                UNION ALL
                SELECT  chain
                FROM    customer
                WHERE   accountnumber = :acc
                )
        CONNECT BY
                c.code = PRIOR c.parentchain
        ) ch
SELECT  *
FROM    campaign
WHERE   c.id IN
        (
        SELECT  campaignid
        FROM    chains 
        JOIN    campaign_eligibility ce
        ON      ce.chaincode = c.code
        )