How to tell if one person is related to another

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 2005 Forums

Transact-SQL (2005)

How to tell if one person is related to another

Author

Topic

muzzettemm
Posting Yak Master

212 Posts

Posted - 2009-08-09 : 02:38:30

I have a table called the Parent_ind. Now in the table can be a parent and parent can be a student, and then children can also be students too. How do I create a child table that will help me indicate if a parent and child are related to one another??

this is what I have so far

the relationship table

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Relationship_tbl](
[FamilyID] [nvarchar](50) NULL,
[Parent ID] [nvarchar](50) NULL,
[Student ID] [nvarchar](50) NULL,
[RelationshipID] [int] IDENTITY(1,1) NOT NULL,
[TypeofRelationship] [nvarchar](50) NULL,
[Description] [nvarchar](50) NULL,
CONSTRAINT [PK_Relationship_tbl] PRIMARY KEY CLUSTERED
(
[RelationshipID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Relationship_tbl] WITH NOCHECK ADD CONSTRAINT [FK_Relationship_tbl_Parent_ind] FOREIGN KEY([Parent ID])
REFERENCES [dbo].[Parent_ind] ([Parent ID])
GO
ALTER TABLE [dbo].[Relationship_tbl] CHECK CONSTRAINT [FK_Relationship_tbl_Parent_ind]

and the Parent_ind table

CREATE TABLE [dbo].[Parent_ind](
[Parent ID] [nvarchar](50) NOT NULL,
[StudentID] [nvarchar](50) NULL,
[Family ID] [nvarchar](50) NULL,
[Date of Referral] [nvarchar](50) NULL,
[Parent First Name] [nvarchar](50) NULL,
[Parent Last Name] [nvarchar](50) NULL,
[Parent SS#] [nvarchar](50) NULL,
[Telephone #] [nvarchar](50) NULL,
[Message #] [nvarchar](50) NULL,
[Address] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
[State] [nvarchar](50) NULL,
[Zip] [nvarchar](50) NULL,
[E Mail Address] [nvarchar](50) NULL,
[Tribal Affiliation] [nvarchar](50) NULL,
[Event ID] [nvarchar](50) NULL,
[TANF staff making Referral] [nvarchar](50) NULL,
[Type Participant] [nvarchar](50) NULL,
[Required hours] [int] NULL,
[Special Instrution] [nvarchar](max) NULL,
[GED] [bit] NULL,
[High School Diploma] [bit] NULL,
[Drivers License] [bit] NULL,
[Assessement Date] [bit] NULL,
[Assessement] [datetime] NULL,
[Career Assessment Date] [bit] NULL,
[Career Assessment] [datetime] NULL,
[Other] [bit] NULL,
[Explain Other] [nvarchar](50) NULL,
[GED/High School Diploma-VC Adult School] [nvarchar](50) NULL,
[Higher Education] [nvarchar](50) NULL,
[Culture] [nvarchar](50) NULL,
[Community Service] [nvarchar](50) NULL,
[Vocational] [nvarchar](50) NULL,
[DMV] [nvarchar](50) NULL,
[RegistrationDate] [datetime] NULL,
[Grade] [nvarchar](50) NULL,
[Age] [nvarchar](50) NULL,
[DOB] [datetime] NULL,
[____] [nvarchar](50) NULL,
[SchoolName] [nvarchar](50) NULL,
[SchoolAddress] [nvarchar](50) NULL,
[SchoolPhone] [nvarchar](50) NULL,
[SchoolCity] [nvarchar](50) NULL,
[SchoolState] [nvarchar](50) NULL,
[SchoolZip] [nvarchar](50) NULL,
[TimeSchoolStarts] [datetime] NULL,
[TimeSchoolEnds] [datetime] NULL,
[StudentGPA] [nvarchar](50) NULL,
[TanfReferral] [nvarchar](50) NULL,
[RefereaLocation] [nvarchar](50) NULL,
[ScannedDocuments] [ntext] NULL CONSTRAINT [DF_Parent_ind_ScannedDocuments] DEFAULT (N'ScannedDocuments'),
CONSTRAINT [PK_Parent_ind] PRIMARY KEY CLUSTERED
(
[Parent ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-09 : 03:54:44

I have posted a lot of algorithms for this in SQLTeam.
Please feel free to search.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=73079
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=125959
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89213

N 56°04'39.26"
E 12°55'05.63"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-09 : 04:58:26

Or, if you are using Celko's Nested Sets,

DECLARE	@Search1 INT,
		@Search2 INT

SELECT	@Search1 = 11,
		@Search2 = 55

IF EXISTS(
			SELECT			*
			FROM			Relationship_tbl AS s1
			INNER JOIN		Relationship_tbl AS s2 ON s2.lft > s1.rgt
								AND s2.rgt < s1.lft
			WHERE			s1.parentID = @Search1
							and s2.parentid = @search2
		)
THEN
	1 -- Related
ELSE
	0 -- Not related

Celko's Nested Sets (CNS) are very efficient for static hierarchies. One do have to remember that every insert of a node in CNS in average need to update 50% of the nodes.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources