Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.sqlserver.programming > #31262

Re: Need to fetch data recursively from a table

Show all headers | View raw

 (siddhant171990@gmail.com) writes:
> What I have is a table with a subset of FTR No's from the master table.
> Now, I want to retrieve all the projects from the master associated with
> these features. 
> 
> Now, since each of these projects can be associated to multiple other
> features, I need to identify those records such that the Instance of the
> projects is always higher than the already existing one. 
> 

There are a few things that are unclear to. For instance, what if we find a 
feature which is related to a project not related to the original set?

Below is a script where I have augmented your test data with a few more 
data points, but I don't know if the result is the expected with regards 
to this data.

CREATE TABLE  tbl(feature  char(4) NOT NULL,
                  project  char(1) NOT NULL,
                  instance int     NOT NULL,
                  PRIMARY KEY (feature, project, instance))

go
INSERT tbl(feature, project, instance)
  VALUES ('FTR1', 'A', 1),
         ('FTR1', 'B', 2),
         ('FTR2', 'A', 2),
         ('FTR2', 'B', 1),
         ('FTR3', 'A', 5),
         ('FTR3', 'C', 5),
         ('FTR4', 'A', 0),
         ('FTR4', 'B', 9)
go
; WITH r AS (
    SELECT feature, project, instance
    FROM   tbl
    WHERE  feature IN ('FTR1', 'FTR2')
    UNION ALL
    SELECT tbl.feature, tbl.project, tbl.instance
    FROM   tbl
    JOIN   r ON tbl.project = r.project
            AND tbl.instance > r.instance
)
SELECT DISTINCT feature, project, instance
FROM   r
go
DROP TABLE tbl



-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

Back to microsoft.public.sqlserver.programming | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Need to fetch data recursively from a table siddhant171990@gmail.com - 2015-08-04 23:20 -0700
  Re: Need to fetch data recursively from a table Erland Sommarskog <esquel@sommarskog.se> - 2015-08-05 10:26 +0200
    Re: Need to fetch data recursively from a table siddhant171990@gmail.com - 2015-08-05 01:57 -0700
      Re: Need to fetch data recursively from a table Erland Sommarskog <esquel@sommarskog.se> - 2015-08-05 13:03 +0200
        Re: Need to fetch data recursively from a table siddhant agarwal <siddhant171990@gmail.com> - 2015-08-05 04:06 -0700
    Re: Need to fetch data recursively from a table siddhant171990@gmail.com - 2015-08-05 02:38 -0700

csiph-web