Finding all parents in a single query with parent and child ranges

1
votes

I have the following table in DB2 with parent and child ranges. Each row contains the parent and the corresponding minimum and maximum value that its children can have:

    parent         child_min   child_max
    ------         ---------   ----------
      1              100           300
      2              500           899
     ...
     ...

Now, I am trying to find the parents of a set of children in a single query.

Example: I have child ids:

    101, 102, 105, 208, 506.

Now the query should return their respective parents 

    1,1,1,1,2.

I did it by CTE . 

    WITH temp(id) AS (VALUES
    101,102,105,208,506)
    SELECT parent FROM table,temp 
    WHERE child_min < = temp.id
    AND child_max >= temp.id

But is there another way to do it without CTE in a single query ?

2
sqldb2parent-child
Your query is a single query. It happens to use a CTE, but that is very reasonable. – Gordon Linoff
Agreed. A Common Table Expression (CTE) is not a "temp table". – Paul Vernon
Um, I have a problem with your schema design: Your child ranges appear to be designating the minimum/maximum id the child can have, which is going to have terrible effects on the design (for one thing, no auto-gen id on that table). Usually, parent/child relationships are done by storing a self-reference (ie, each child stores its parent). Also, your upper-bound is inclusive, which irks me (but isn't too big an issue) – Clockwork-Muse

2 Answers

1
votes

It's just a simple JOIN. Considering the following data (I added an extra row):

create table parent_children (
  parent int,
  child_min int,
  child_max int
);

insert into parent_children (parent, child_min, child_max) values (1, 100, 300);
insert into parent_children (parent, child_min, child_max) values (2, 500, 899);

The query would be:

with children (id) as (values 101, 102, 105, 208, 506, 1000)
select c.id, p.parent
  from children c
  left join parent_children p on c.id between p.child_min and p.child_max;

Result:

ID          PARENT       
----------  -------
101         1            
102         1            
105         1            
208         1            
506         2            
1000        <null>
0
votes

Do you prefer this?

SELECT parent
FROM table
, TABLE(VALUES  101,102,105,208,506) AS t(id) 
WHERE child_min <= t.id
AND child_max >= t.id;