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

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.

CodePudding user response：

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;