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

Need to fetch data recursively from a table

X-Received	by 10.182.106.11 with SMTP id gq11mr6587380obb.25.1438755611143; Tue, 04 Aug 2015 23:20:11 -0700 (PDT)
X-Received	by 10.182.33.10 with SMTP id n10mr40805obi.18.1438755611073; Tue, 04 Aug 2015 23:20:11 -0700 (PDT)
Path	csiph.com!optima2.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!f3no7703857igg.0!news-out.google.com!o13ni7098igw.0!nntp.google.com!f3no7703846igg.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups	microsoft.public.sqlserver.programming
Date	Tue, 4 Aug 2015 23:20:10 -0700 (PDT)
Complaints-To	groups-abuse@google.com
Injection-Info	glegroupsg2000goo.googlegroups.com; posting-host=115.248.211.41; posting-account=UdCuKwoAAAB0-uLpIoFcSSp1N3kkQ94p
NNTP-Posting-Host	115.248.211.41
User-Agent	G2/1.0
MIME-Version	1.0
Message-ID	<ee908b1e-167e-466e-9c16-9d1482d15e6e@googlegroups.com> (permalink)
Subject	Need to fetch data recursively from a table
From	siddhant171990@gmail.com
Injection-Date	Wed, 05 Aug 2015 06:20:11 +0000
Content-Type	text/plain; charset=ISO-8859-1
Xref	csiph.com microsoft.public.sqlserver.programming:31261

Show key headers only | 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