X-Received: by 10.140.144.17 with SMTP id 17mr7275810qhq.6.1438765046010; Wed, 05 Aug 2015 01:57:26 -0700 (PDT) X-Received: by 10.182.116.197 with SMTP id jy5mr43340obb.24.1438765045927; Wed, 05 Aug 2015 01:57:25 -0700 (PDT) Path: csiph.com!au2pb.net!usenet.blueworldhosting.com!feeder01.blueworldhosting.com!peer03.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!z61no5342040qge.0!news-out.google.com!nt1ni142igb.0!nntp.google.com!pg9no7202255igb.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: microsoft.public.sqlserver.programming Date: Wed, 5 Aug 2015 01:57:25 -0700 (PDT) In-Reply-To: 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 References: User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: Subject: Re: Need to fetch data recursively from a table From: siddhant171990@gmail.com Injection-Date: Wed, 05 Aug 2015 08:57:25 +0000 Content-Type: text/plain; charset=ISO-8859-1 X-Received-Bytes: 3650 X-Received-Body-CRC: 2282813958 Xref: csiph.com microsoft.public.sqlserver.programming:31263 On Wednesday, 5 August 2015 13:57:01 UTC+5:30, Erland Sommarskog wrote: > (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 Hi, - First, Find projects related to original set of features. Each feature can relate to only instance of a project. But, it can relate to many projects. - Secondly, since some other instance of the projects found above may be related to some features, I need to identify those features as well and any other projects which these features are also related too. This will go on recursively. - Thirdly, every time we fetch features for the projects, we fetch only those features that use the instance of the projects greater than the on found already. - All in all, there should be some path starting for the initial feature list to all records. - Does this help?