CODEWITHSUNDEEP
sqloracle-database
yas
yas

Reputation: 3620

How to use an SQL subquery to restrict the range of values returned in the parent query?

I have a query I'd like to use to define a range as input to another query. This subquery returns two values, a pre-order traversal index and the size of a subtree. For example,

SELECT
    ds.pre,
    ds.sze
FROM 
    form_data_stage ds
WHERE
    and ds.fdm_pre = 10
    and ds.pre < 16 and (ds.pre + ds.sze) > 16

This query will always return a single row, with the pre-order index and size.

The parent query needs to do something along these lines:

SELECT
    ds.pre,
    ds.sze,
    ds.fdm_pre
FROM
    form_data_stage ds
WHERE
    ds.pre > (pre /* i.e. pre value from the subquery */) 
    and ds.pre < ( (pre + sze) /* i.e. (pre + sze) from the same subquery */)

The subquery essentially defines the range of pre values the parent query should return. I'm not sure how to do accomplish this with SQL.

Upvotes: 0

Views: 73

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

Use your 1st query as a CTE and join it with a table in your 2nd query; something like this:

with first_query as
  (SELECT
    ds.pre,
    ds.sze
   FROM 
    form_data_stage ds
   WHERE
        ds.fdm_pre = 10
    and ds.pre < 16 and (ds.pre + ds.sze) > 16
  )
SELECT
    ds.pre,
    ds.sze,
    ds.fdm_pre
FROM
    form_data_stage ds join first_query a on ds.pre > a.pre
                                         and ds.pre < a.pre + ds.sze;

Upvotes: 1

Related Questions