Need to fetch data recursively from a table

[s***@gmail.com]

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?

[Erland Sommarskog]

Post by s***@gmail.com
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

[s***@gmail.com]

Post by Erland Sommarskog

Post by s***@gmail.com
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
--

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?

[Erland Sommarskog]

Post by s***@gmail.com
- 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.

So this is illegal?

INSERT tbl(feature, project, instance)
VALUES ('FTR1', 'A', 1),
('FTR1', 'A', 2)

But this is legal?

INSERT tbl(feature, project, instance)
VALUES ('FTR1', 'A', 1),
('FTR1', 'B', 1)


That is, the instance relates to the project alone?

Post by s***@gmail.com
- 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.

Please supply a wider set of test data with the expected results. This
helps to clarify your description.

[siddhant agarwal]

Post by Erland Sommarskog

Post by s***@gmail.com
- 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.

So this is illegal?
INSERT tbl(feature, project, instance)
VALUES ('FTR1', 'A', 1),
('FTR1', 'A', 2)
But this is legal?
INSERT tbl(feature, project, instance)
VALUES ('FTR1', 'A', 1),
('FTR1', 'B', 1)
That is, the instance relates to the project alone?

Post by s***@gmail.com
- 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.

Please supply a wider set of test data with the expected results. This
helps to clarify your description.
--

Yes, you cannot have 2 instances of the same project related to same feature.

[s***@gmail.com]

Post by Erland Sommarskog

Post by s***@gmail.com
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
--

It doesn't work. I get the same record multiple times.