how to get parent classes in sql server

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

General SQL Server Forums

New to SQL Server Programming

how to get parent classes in sql server

Author

Topic

nitin05.sharma
Starting Member

20 Posts

Posted - 2011-12-05 : 03:58:40

hi

i am using oops concept in sql like inheritence there five class A-B-C-D-E

B inheriting A

C inheriting B

D inheriting C

E inheriting D

i want to write a querry whenevere i'll get a input E output should be A,B,C,D

its urgent please reply me soon

thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-05 : 04:13:43

look for recursive ctes . i think that what you're looking at

http://msdn.microsoft.com/en-us/library/ms186243.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sureshkk
Starting Member

21 Posts

Posted - 2011-12-05 : 04:19:15

CREATE TABLE #Refences
(
ObjectID INT,
ObjectName VARCHAR(10),
RefObject INT
)
GO

INSERT INTO #Refences
SELECT 1,'A',NULL
UNION ALL
SELECT 2,'B',1
UNION ALL
SELECT 3,'C',2
UNION ALL
SELECT 4,'D',3
UNION ALL
SELECT 5,'E',4

DECLARE @RefObj INT

SET @RefObj=5

;WITH CTE(ObjectID,ObjectName,RefObject)
AS
(
SELECT B.ObjectID,B.ObjectName,B.RefObject
FROM #Refences A
INNER JOIN #Refences B
ON A.RefObject=B.ObjectID
WHERE A.ObjectID=@RefObj
UNION ALL
SELECT R.ObjectID,R.ObjectName,R.RefObject
FROM #Refences R
INNER JOIN CTE C
ON C.RefObject=R.ObjectID
)
SELECT * FROM CTE

DROP TABLE #Refences

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-05 : 04:23:32

quote:
Originally posted by sureshkk

CREATE TABLE #Refences
(
ObjectID INT,
ObjectName VARCHAR(10),
RefObject INT
)
GO

INSERT INTO #Refences
SELECT 1,'A',NULL
UNION ALL
SELECT 2,'B',1
UNION ALL
SELECT 3,'C',2
UNION ALL
SELECT 4,'D',3
UNION ALL
SELECT 5,'E',4

DECLARE @RefObj INT

SET @RefObj=5

;WITH CTE(ObjectID,ObjectName,RefObject)
AS
(
SELECT B.ObjectID,B.ObjectName,B.RefObject
FROM #Refences A
INNER JOIN #Refences B
ON A.RefObject=B.ObjectID
WHERE A.ObjectID=@RefObj
UNION ALL
SELECT R.ObjectID,R.ObjectName,R.RefObject
FROM #Refences R
INNER JOIN CTE C
ON C.RefObject=R.ObjectID
)
SELECT * FROM CTE

DROP TABLE #Refences

the above join is not required

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sureshkk
Starting Member

21 Posts

Posted - 2011-12-05 : 04:29:37

Hi Visakh,
If i remove join from the above query it displays Record 'E' also.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-12-05 : 04:39:05

quote:
Originally posted by sureshkk

Hi Visakh,
If i remove join from the above query it displays Record 'E' also.

oh ..so you dont want to show main record details?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nitin05.sharma
Starting Member

20 Posts

Posted - 2011-12-05 : 04:58:57

thanks vikas its running fine
thanks a lot

quote:
Originally posted by visakh16

quote:
Originally posted by sureshkk

CREATE TABLE #Refences
(
ObjectID INT,
ObjectName VARCHAR(10),
RefObject INT
)
GO

INSERT INTO #Refences
SELECT 1,'A',NULL
UNION ALL
SELECT 2,'B',1
UNION ALL
SELECT 3,'C',2
UNION ALL
SELECT 4,'D',3
UNION ALL
SELECT 5,'E',4

DECLARE @RefObj INT

SET @RefObj=5

;WITH CTE(ObjectID,ObjectName,RefObject)
AS
(
SELECT B.ObjectID,B.ObjectName,B.RefObject
FROM #Refences A
INNER JOIN #Refences B
ON A.RefObject=B.ObjectID
WHERE A.ObjectID=@RefObj
UNION ALL
SELECT R.ObjectID,R.ObjectName,R.RefObject
FROM #Refences R
INNER JOIN CTE C
ON C.RefObject=R.ObjectID
)
SELECT * FROM CTE

DROP TABLE #Refences

the above join is not required

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources