Temp table of possible Combinations

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Temp table of possible Combinations

Author

Topic

Billpl
Yak Posting Veteran

71 Posts

Posted - 2004-11-19 : 15:43:11

Trying to do something tricky. This is sort of a "roll-up" table which has an identity ID field and a new identity field which basically replaces the old. Without actually explaining why, I am trying to achieve the following result.

From Table

Primary ID New Primary ID
--------- ---------------

101, 178
178, 210
210, 345
345, null

ResultSet showing all combinations forward only

Primary ID New Primary ID
--------- ---------------

101, 178
101, 210
101, 345
178, 210
178, 345
210, 345

I have a rollup function that can return any a previous ID but I would prefer to create a Temp table instead.

Any Clue?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-19 : 15:46:42

this helps?


declare @b table (id1 int, id2 int)
insert into @b
select 101, 178 union all
select 178, 210 union all
select 210, 345 union all
select 345, null

select * from @b

select t1.id1, t2.id2
from @b t1, @b t2
where t1.id1 < t2.id2
order by t1.id1

Go with the flow & have fun! Else fight the flow

Billpl
Yak Posting Veteran

71 Posts

Posted - 2004-11-19 : 16:22:39

Kind of the idea but, the first table is a Subset of a very large table which serves as a parent to others. Plus the ID's for the most part will be in order but can't be assumed.

I should have included that the resultSet represents a query based on the originating record "101". If the query were based on "178" the records with "101" would not be included.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-19 : 16:26:41

eliminating 101 is simple
where t1.id1 > 101

as for the larger query... doing what you need is probably fastest this way... but for better help we'd need better info.

Go with the flow & have fun! Else fight the flow

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2004-11-19 : 17:49:29

If I catch your drift, this is really a question about multi-level hierarchies. I don't have the time to provide you a link, but research the topic a little more using those keywords.

Billpl
Yak Posting Veteran

71 Posts

Posted - 2004-11-19 : 18:20:17

quote:
Originally posted by spirit1

... but for better help we'd need better info.

What's really happening here is too complicated to explain, plus it applies to two totally different applications but both use similar relationships...so I'll try it this way. It can currently run using a rollup function that looks something like this (I just eye-balled this, I don't have access to the server right now)

----------------------------------------
Select (whatever)
from Lots, child1, child2,..
Where LotID = dbo.Included_in_Lot_Roll_up ( '101', LotID, NewLotID )
......
----------------------------------------
CREATE FUNCTION dbo.Included_in_Lot_Roll_up (

@DesignatedLotID as Int,
@RecordLotID as Int,
@RecordNewLotID as Int )

RETURNS Int
AS
BEGIN
Declare @MatchToRecord as Int

Select @MatchToRecord =
Case When @DesignatedLotID = @RecordLotID
Then @RecordLotID

When @DesignatedLotID = @RecordNewLotID
Then @RecordNewLotID

When @RecordNewLotID is null
Then -1

Else( Select dbo.Included_in_Lot_Roll_up( @RecordLotID, LotID, NewLotID )
from Lots Where Lotid = @RecordNewLotID )

End
Return @MatchToRecord
END

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-11-19 : 18:42:09

recursion....uuuuuu.... that's trees...
well for trees you need a subquery for each child you have.

maybe some of these links will give you some ideas:

http://www.sqlteam.com/searchresults.asp?SearchTerms=tree

Go with the flow & have fun! Else fight the flow

Billpl
Yak Posting Veteran

71 Posts

Posted - 2004-11-20 : 12:22:05

Well Recursion did the trick. In this case recursion of recursion. Thanks spirit1, the articles on trees tipped me off.
(...when you see me working on queries on a friday night...you know I'm sweating it :) )

For whatever it's worth here's the solution.
--------------------------------------------
CREATE TABLE [Test_Rollup] (
[pkID] [int] NULL ,
[NewPkID] [int] NULL
) ON [PRIMARY]
GO

insert into test_rollup values (101,174)
insert into test_rollup values (174,210)
insert into test_rollup values (210,479)
insert into test_rollup values (479,510)
insert into test_rollup values (510,NULL)
insert into test_rollup values (100,NULL)
insert into test_rollup values (197,NULL)
insert into test_rollup values (144,217)
insert into test_rollup values (217,388)
insert into test_rollup values (388,455)
insert into test_rollup values (455,NULL)

---------------------------------------------
CREATE FUNCTION dbo.Included_in_Lot_Roll_up (

@DesignatedPKID as Int,
@RecordPKID as Int,
@RecordNewPKID as Int )

RETURNS Int
AS
BEGIN
Declare @MatchToRecord as Int

Select @MatchToRecord =
Case When @DesignatedPKID = @RecordPKID
Then @RecordPKID

When @RecordNewPKID is null
Then -1

Else( Select dbo.Included_in_Lot_Roll_up( @DesignatedPKID, r.PKID, r.NewPKID )
from test_rollup r Where r.PKid = @RecordNewPKID )

End
Return @MatchToRecord
END
go

----------------------------------------------
Declare @upto as int
Set @upto = 510 -- keyed ID number

Select a.pkid, b.pkid

From test_rollup a,test_rollup b

Where dbo.Included_in_Lot_Roll_up ( @upto , a.pkID, a.NewPkID ) = @upto
and dbo.Included_in_Lot_Roll_up ( a.pkID, b.pkID, b.NewPkID ) = a.pkID

----------------------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources