How to Select All Continuous rows joining by begin and end date-CodePudding

I have a situation where I have multiple rows in a table but they can be assigned to a common name id

NAMEID	ID	STARTDATE	ENDDATE
10960	1	17-FEB-20	17-FEB-20
11210	2	17-FEB-20	18-FEB-20
10960	3	19-FEB-20	21-FEB-20
10960	4	21-FEB-20	02-MAR-20
11461	5	21-FEB-20	02-MAR-20
10960	6	01-MAR-20	06-MAR-20
10960	7	02-MAR-20	04-MAR-20

let's say I want to query by ID 7 and name_id 10960 I want to select rows where ID 7,4,3 because these are connecting records based on start and end date.

CodePudding user response：

You can use a recursive CTE to get all related rows.

For example:

with
n (nameid, id, startdate, enddate) as (
  select nameid, id, startdate, enddate from t where id = 7 -- starting row
 union all
  select t.nameid, t.id, t.startdate, t.enddate
  from n
  join t on t.nameid = n.nameid and t.enddate = n.startdate
)
select * from n;

Result:

 NAMEID  ID  STARTDATE  ENDDATE   
 ------- --- ---------- --------- 
 10960   7   02-MAR-20  04-MAR-20 
 10960   4   21-FEB-20  02-MAR-20 
 10960   3   19-FEB-20  21-FEB-20

See example at db<>fiddle.

CodePudding user response：

You can use CONNECT BY to return the rows by selecting your starting point and limiting which nameids you want traversed.

    SELECT *
      FROM names
CONNECT BY enddate = PRIOR startdate AND nameid = PRIOR nameid
START WITH id = 7;

   NAMEID    ID    STARTDATE      ENDDATE
_________ _____ ____________ ____________
    10960     7 02-MAR-20    04-MAR-20
    10960     4 21-FEB-20    02-MAR-20
    10960     3 19-FEB-20    21-FEB-20