Self Join - query reg - SQL Server Forums

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)

Self Join - query reg

Author

Topic

karkey
Starting Member

5 Posts

Posted - 2008-04-08 : 07:35:00

I've a table like

Object ObjId ParentObjId
------ ----- -----------
Obj1 | 101 | 0
Obj2 | 102 | 101
Obj3 | 103 | 0
Obj4 | 104 | 102

If the 'ParentObjId' is 0 it means no more parent,

If I filter the Object value as Obj4, it should show the following rows

Obj4 | 104 | 102
Obj2 | 102 | 101
Obj1 | 101 | 0
Guide to me write a SQL for this situvation?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-04-08 : 07:40:36

moved from script library.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-04-08 : 08:00:31

http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-08 : 08:00:41

with mycte (ObjID, ParentObjID)
as
(
select ObjID, ParentObjID from tbl where Object = 'Obj4'
union all
select ObjID, ParentObjID from tbl join mycsv on mycsv.ParentObjID = tbl.ObjID
)
select *
from tbl
where ObjID in (select ObjID from mycte) a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 08:14:47

[code]DECLARE @Sample TABLE (ObjectName CHAR(4), ObjectID TINYINT, ParentObjectID TINYINT)

INSERT @Sample
SELECT 'Obj1', 101, 0 UNION ALL
SELECT 'Obj2', 102, 101 UNION ALL
SELECT 'Obj3', 103, 0 UNION ALL
SELECT 'Obj4', 104, 102

;WITH Yak (ObjectName, ObjectID, ParentObjectID, Lvl)
AS (
SELECT ObjectName,
ObjectId,
ParentObjectID,
0
FROM @Sample
WHERE ObjectName = 'Obj4'

UNION ALL

SELECT s.ObjectName,
s.ObjectId,
s.ParentObjectID,
y.Lvl + 1
FROM @Sample AS s
INNER JOIN Yak AS y ON y.ParentObjectID = s.ObjectID
)

SELECT ObjectName,
ObjectID,
ParentObjectID
FROM Yak
ORDER BY Lvl[/code]

E 12°55'05.25"
N 56°04'39.16"

karkey
Starting Member

5 Posts

Posted - 2008-04-08 : 09:41:20

Thanks Peso,
I'm getting below error while executing the query

'Recursion not allowed without the RECURSIVE keyword'

SQLCODE=-938

Thanks and regards
Karkey

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-08 : 09:44:03

You are not using MICROSOFT SQL SERVER, are you?

E 12°55'05.25"
N 56°04'39.16"

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-08 : 09:49:16

sybase?

Em

karkey
Starting Member

5 Posts

Posted - 2008-04-08 : 09:52:19

Yes I'm using Sybase

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-08 : 09:53:25

What?????

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-08 : 09:54:33

i'm afraid you'll be best to find a forum for sybase then. this site is dedicated to Sql Server

Em

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-08 : 09:55:50

Let us know If you want to migrate sybase to SQL server. I have done it.

karkey
Starting Member

5 Posts

Posted - 2008-04-08 : 10:01:06

Its working fine SQL server, while executing the query in Sybase I'm getting the error, Could you help to solve this issue?

Thanks
Karkey

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-08 : 10:03:13

Becoz this forum is only for SQL yaks. Find sybase forum.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-08 : 10:03:45

well no actually. sybase will be different syntax, so you need to ask at a sybase forum

Em

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-08 : 10:05:50

Thats why you need to migrate in SQL server .

karkey
Starting Member

5 Posts

Posted - 2008-04-10 : 11:35:38

Hello Peso & nr,

Thanks a lot!! its working fine in Sybase also, just I added 'RECURSIVE' keyword after 'with'.

-karkey

romicva
Starting Member

2 Posts

Posted - 2009-05-12 : 10:47:09

What if my table holds hierarchy of 3 not 2 levels and I need to display them in pivoted manner?
I've been pooling my hair for the last 30+ hours. Any help would be greatly appretiated. Thank you. Roman.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources