December 5, 2011 at 1:08 am
Hi All,
I hope you can help. I've got a membership table that includes a column "MembershipNumber" which is the members number for a specific group. For example, I could be member #1 in Group 1, but member #9 in Group 2.
I'm successfully inserting/updating this value, however when it comes to deleting I've come across a bit of a stumbling block. When a member leaves a group, their membership number gets "reallocated" to the next member in line. So, in my example above, if member #2 leaves from Group 2, everyone after #2 will have their membership number moved up, thus removing any gaps like 1..3,4,5,6,etc.
Here's some code that will create and populate a cut down version of the table.
IF OBJECT_ID('dbo.GroupMembership') IS NOT NULL
DROP TABLE dbo.GroupMembership
GO
CREATE TABLE dbo.GroupMembership (
GroupID BIGINT NOT NULL
,UserID BIGINT NOT NULL
,MembershipNumber INTEGER NOT NULL
,CreateDate DATETIME NOT NULL DEFAULT(GETDATE())
)
GO
INSERT INTO GroupMembership(GroupID,UserID,MembershipNumber) VALUES(1,1,1)
INSERT INTO GroupMembership(GroupID,UserID,MembershipNumber) VALUES(1,2,2)
INSERT INTO GroupMembership(GroupID,UserID,MembershipNumber) VALUES(1,3,3)
INSERT INTO GroupMembership(GroupID,UserID,MembershipNumber) VALUES(1,6,4)
INSERT INTO GroupMembership(GroupID,UserID,MembershipNumber) VALUES(2,4,1)
INSERT INTO GroupMembership(GroupID,UserID,MembershipNumber) VALUES(2,5,2)
INSERT INTO GroupMembership(GroupID,UserID,MembershipNumber) VALUES(3,1,1)
INSERT INTO GroupMembership(GroupID,UserID,MembershipNumber) VALUES(3,3,2)
SELECT *
FROM GroupMembership AS gm
DROP TABLE dbo.GroupMembership
Thanks to all in advance. As always, any help is gratefully received!
Kevin.
December 5, 2011 at 1:48 am
This was removed by the editor as SPAM
December 5, 2011 at 2:05 am
Hi Stewart,
Thanks for the reply. I'll try it out, but from the looks of it it's exactly what I need!
It's annoying when the solution is so simple, but I just couldn't get my head around the solution!
Cheers,
Kev.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply