Hierarchical data

Question

Hierarchical data

hoseam

SSCertifiable

Points: 5533
More actions
June 13, 2016 at 2:00 am

#317118

Hi,
I have members that belongs to different group and other member can be in more than one group but in Hierarchy.
I.E Group One, these members are only found in this group
Group Two, Some, not all, of these members can also be found in Group One
Group Three, Some, not all, of these members can also be found in Group Two
Group Four, Some, not all, of these members can also be found in Group Three
All of these are found in these three Tables,
CREATE TABLE [dbo].[Groups](
[GroupID] [tinyint] NOT NULL,
[GroupName] [varchar](50) NOT NULL
)
CREATE TABLE [dbo].[Subscriptions](
[SubscriptionID] [bigint] IDENTITY(1000,1) NOT NULL,
[Status] [int] NOT NULL,
[StartDate] [datetime] NULL,
[ActivationDate] [datetime] NULL,
[EndDate] [datetime] NULL,
[PersonID] [bigint] NULL,
[GroupID] [tinyint] NULL)
CREATE TABLE [dbo].[HD_PERSON](
[PersonID] [bigint] IDENTITY(1,1) NOT NULL,
[FirstName] [varchar](100) NULL,
[LastName] [varchar](200) NULL)
So if I join these table and SELECT DISTINCT, if PersonID from GROUP 2 is also in Group 1, that person will also be returned.
So I want all PersonID in that Hierarchy, All of Group 1 members, the Group 2 but not in Group until the last Group.
Please help

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76996 More actions · Answer 1

After 390+ visits, you should know how to post data to a forum to get the best results. I have a link in my signature if you need a reminder.

Just reading the description, it sounds like MIN(GroupID) should give you what you need.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 2

@drew,

The first link in your signature line takes us to an MSDN site for "ORDER BY".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

drew.allen SSC Guru Points: 76996 More actions · Answer 3

@jeff,

Thanks. It should be fixed now.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

GSquared SSC Guru Points: 260824 More actions · Answer 4

For this, I'd use a many-to-many table with a HierarchyID datatype column that allowed each person to be in multiple hierarchies.

Or will you have depths greater than about 400 nodes? HierarchyID works well above that, but can't really go below that.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon