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

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

Need to fetch data recursively from a table

Show all headers | View raw

I have a master table like this - 

    Table(Feature, PrjctNo, Instance)

Each record here is unique. Say,

    FTR1, PrjctA, Instance1
    FTR1, PrjctB, Instance2
    FTR2, PrjctA, Instance2
    FTR2, PrjctB, Instance1
    FTR3, PrjctA, Instance5
    FTR4, PrjctA, Instance0

Basically, each feature can be mapped to multiple projects. And each project can be mapped to multiple feature but the instance of that project for each feature will be unique.

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.

Ex : Given that I have a subset (FTR1, FTR2)

Iteration 1 :

    FTR1, PrjctA, Instance1
    FTR1, PrjctB, Instance2
    FTR2, PrjctA, Instance2
    FTR2, PrjctB, Instance1

Iteration 2: Check if there any other features associated to any of the projects fetched above. So, after iteration 2, the result set should look like :

    FTR1, PrjctA, Instance1
    FTR1, PrjctB, Instance2
    FTR2, PrjctA, Instance2
    FTR2, PrjctB, Instance1
    FTR3, PrjctA, Instance5

We won't add the record with Instance0 of PrjctA. We always have to fetch the higher instance.

How can I accomplish this using SQL server?

Back to microsoft.public.sqlserver.programming | Previous | Next — 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