April 15, 2011 at 6:43 am
Hi,
I have a stored procedure which creates a given number of cloned entities.
These entities are then said to be "enemies" of each other because they are identical.
I need to populate the enemies table with all unique combinations of the new items.
For example, I start with a list of entities: 32, 54, 67, 148.
I would need to insert the following values into the enemies table.
OriginalID | EnemyID
148 | 54
148 | 32
148 | 67
67 | 54
67 | 32
32 | 54
Please note that the OriginalID and EnemyID are interchangeable. The following would be invalid data:
OriginalID | EnemyID
67 | 54
54 | 67
Is there a way to do this without a loop?
Sample code to play with:
-- a real table in our database
DECLARE @Enemies TABLE
(
OriginalIDBIGINT
,EnemyIDBIGINT
)
-- a temp table in the stored procedure
DECLARE @NewEntities TABLE
(
IDINT IDENTITY(1,1)
,EntityIDBIGINT
)
-- some dummy data
INSERT INTO @NewEntities
SELECT 54
UNION ALL SELECT 32
UNION ALL SELECT 67
UNION ALL SELECT 148
-- can't have items and enemies that are the same
-- this would not be valid:
-- OriginalID| EnemyID
-- 54 | 32
-- 32| 54
-- is there a way to do this without a loop?
DECLARE @iINT
, @IDBIGINT
SELECT @i = MAX(ID)
FROM @NewEntities
WHILE @i > 0
BEGIN
SELECT @ID = EntityID
FROM @NewEntities
WHERE ID = @i
INSERT INTO @Enemies(OriginalID, EnemyID)
SELECT @ID
, EntityID
FROM @NewEntities NI
LEFT JOIN @Enemies E1 ON NI.EntityID = E1.OriginalID AND @ID = E1.EnemyID
LEFT JOIN @Enemies E2 ON @ID = E2.OriginalID AND NI.EntityID = E2.EnemyID
WHERE EntityID <> @ID
AND E1.OriginalID IS NULL
AND E2.OriginalID IS NULL
SET @i -= 1
END
SELECT *
FROM @Enemies
April 15, 2011 at 6:53 am
Goldie Lesser (4/15/2011)
Hi,.............
For example, I start with a list of entities: 32, 54, 67, 148.
I would need to insert the following values into the enemies table.
OriginalID | EnemyID
148 | 54
148 | 32
148 | 67
67 | 54
67 | 32
32 | 54
Please note that the OriginalID and EnemyID are interchangeable. The following would be invalid data:
OriginalID | EnemyID
67 | 54
54 | 67
..........
These two statements seem to contradict each other. Why would 67 - 54 be invalid?
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgApril 15, 2011 at 6:55 am
Jason Selburg (4/15/2011)
Goldie Lesser (4/15/2011)
Hi,.............
For example, I start with a list of entities: 32, 54, 67, 148.
I would need to insert the following values into the enemies table.
OriginalID | EnemyID
148 | 54
148 | 32
148 | 67
67 | 54
67 | 32
32 | 54
Please note that the OriginalID and EnemyID are interchangeable. The following would be invalid data:
OriginalID | EnemyID
67 | 54
54 | 67
..........
These two statements seem to contradict each other. Why would 67 - 54 be invalid?
Because they mean the same thing. If 67 is an enemy of 54 then 54 is an enemy of 67.
We only want the data once in the table.
April 15, 2011 at 7:12 am
Try the following
SELECT t2.EntityID, t1.EntityID
FROM @NewEntities t1
CROSS JOIN @NewEntities t2
WHERE t1.ID < t2.ID
ORDER BY t2.ID DESC
April 15, 2011 at 7:25 am
DB Dan (4/15/2011)
Try the following
SELECT t2.EntityID, t1.EntityID
FROM @NewEntities t1
CROSS JOIN @NewEntities t2
WHERE t1.ID < t2.ID
ORDER BY t2.ID DESC
That worked! Thanks!
April 15, 2011 at 7:53 am
DB Dan (4/15/2011)
Try the following
SELECT t2.EntityID, t1.EntityID
FROM @NewEntities t1
CROSS JOIN @NewEntities t2
WHERE t1.ID < t2.ID
ORDER BY t2.ID DESC
Dang-It Dan!!!!
That was TOO EASY. I was going down a rabbit hole with CTE's.
Nice simple solution to what seemed to be very complicated. 😀
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply