The following is a fictitious example of a With Recursive Query to select all of the customers referred to a business directly or indirectly by customer 1362480.
In support of this and subsequent examples in this section, the following test table is needed.
DROP TABLE twm_results._twm_referral; CREATE TABLE twm_results._twm_referral (cust_id INTEGER , referred_by INTEGER); INSERT INTO twm_results._twm_referral VALUES (1362480, NULL); INSERT INTO twm_results._twm_referral VALUES (1362481, 1362480); INSERT INTO twm_results._twm_referral VALUES (1362484, 1362480); INSERT INTO twm_results._twm_referral VALUES (1362485, 1362480); INSERT INTO twm_results._twm_referral VALUES (1362486, 1362481); INSERT INTO twm_results._twm_referral VALUES (1362487, 1362481); INSERT INTO twm_results._twm_referral VALUES (1362488, 1362487); INSERT INTO twm_results._twm_referral VALUES (1362489, NULL); INSERT INTO twm_results._twm_referral VALUES (1362492, 1362489); INSERT INTO twm_results._twm_referral VALUES (1362498, 1362489); INSERT INTO twm_results._twm_referral VALUES (1362499, 1362489); INSERT INTO twm_results._twm_referral VALUES (1362500, 1362499); INSERT INTO twm_results._twm_referral VALUES (1362501, 1362499);
The table of interest in this example contains two columns, cust_id and referred_by, which define a hierarchical relationship within the table, represented by the following example.
1362480 <- 1362481 <- 1362486 <- 1362487 <- 1362488 <- 1362484 <- 1362485 1362489 <- 1362492 <- 1362498 <- 1362499 <- 1362500 <- 1362501
The With (Recursive) Clause in the base query analysis looks like the following, produced by selecting cust_id from Referral Seed and from Referral Recursion.
The seed query Referral Seed selects cust_id from the table of interest along with a constant 0 for level using the select list below, while the Where clause below “starts” the query by comparing the referred_by column in the table of interest to 1362480:
The recursive query Referral Recursion selects cust_id from the table of interest and level + 1 from Referral Seed with the following select list, along with a Where clause matching cust_id from Referral Seed to referred_by in the table of interest, while also limiting the recursion level from Referral Seed to 3.
The SQL generated by the base query follows.
WITH RECURSIVE "Referral Seed" ("cust_id", "level") AS (SELECT "_twmSQ0"."cust_id" AS "cust_id" ,0 AS "level" FROM "twm_results"."_twm_referral" AS "_twmSQ0" WHERE "_twmSQ0"."referred_by" = 1362480 UNION ALL SELECT "_twmVC0"."cust_id" AS "cust_id" ,"_twmVC1"."level" + 1 AS "level" FROM "twm_results"."_twm_referral" AS "_twmVC0" , "Referral Seed" AS "_twmVC1" WHERE ("_twmVC1"."cust_id" = "_twmVC0"."referred_by") AND ("_twmVC1"."level" <= 3) ) SELECT "_twmVC0"."cust_id" AS "cust_id" FROM "Referral Seed" AS "_twmVC0" ORDER BY "_twmVC0"."cust_id";
The result set consists of all of the customers referred directly or indirectly by 1362480.
cust_id 1362481 1362484 1362485 1362486 1362487 1362488