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

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

Need to fetch data recursively from a table

Back to article view | Back to microsoft.public.sqlserver.programming

Contents

  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

#31261 — Need to fetch data recursively from a table

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?

[toc] | [next] | [standalone]

#31262

 (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

[toc] | [prev] | [next] | [standalone]

#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?

[toc] | [prev] | [next] | [standalone]

#31265

 (siddhant171990@gmail.com) writes:
>  - 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?

> - 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.



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

[toc] | [prev] | [next] | [standalone]

#31266

On Wednesday, 5 August 2015 16:33:24 UTC+5:30, Erland Sommarskog  wrote:
> (siddhant171990@gmail.com) writes:
> >  - 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?
> 
> > - 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.
> 
> 
> 
> -- 
> Erland Sommarskog, Stockholm, esquel@sommarskog.se

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

[toc] | [prev] | [next] | [standalone]

#31264

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

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

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.sqlserver.programming

csiph-web