Self join query - 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

Author

Topic

khanewal
Starting Member

33 Posts

Posted - 2010-08-24 : 04:09:36

Hi can plz help me out
Can i get results 1 2 3 and 4 col b is ref id
Using self join
Id. Col A. Col B
1. Abc. 0
2. Cde. 0
3. Ef. 1
4. Gh. 2
5. Ij. 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-24 : 07:46:48

didnt get that. can you elaborate what exactly are you looking at?

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

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-08-24 : 08:15:50

You could mean so many things by that question.

This gives you what you asked for but I'm not sure that's actually what you wanted.


DECLARE @foo TABLE (
	[ID] INT
	, [ColA] VARCHAR(3)
	, [colB] INT
	)

INSERT @foo
      SELECT 1, 'Abc', 0
UNION SELECT 2, 'Cde', 0
UNION SELECT 3, 'Ef', 1
UNION SELECT 4, 'Gh', 2
UNION SELECT 5, 'Ij', 0

; WITH
	heirarchy

	AS (
		SELECT
			[ID], [colA], [colb]
		FROM
			@foo
		WHERE
			[colb] <> 0
		UNION ALL SELECT
			f2.[ID], f2.[colA], f2.[colb]
		FROM
			@foo AS f2
			JOIN heirarchy h ON h.[colB] = f2.[ID]
		)

SELECT * FROM heirarchy ORDER BY [ID]

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

khanewal
Starting Member

33 Posts

Posted - 2010-08-24 : 20:03:10

Hi Charlie,

Thanks for your reply,

khanewal
Starting Member

33 Posts

Posted - 2010-08-25 : 01:39:08

Query:

SELECT
[ID] AS ParentID, [parent_ID] AS ChildID,[theNote], [Page_ID]
FROM
dbo.main_notes
WHERE
[main_ID] = 39

Output:
Parent Child Page No
28 0 42
30 28 0
31 30 0
32 31 0
33 0 43
34 33 0
35 34 0
38 33 0

there is a query and output, but I need to insert page_ID number in the output like that

REQUIRED OUTPUT:

Parent Child Page NO
28 0 42
30 28 42
31 30 42
32 31 42
33 0 43
34 33 43
35 34 43

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-25 : 09:50:19

you need to create function like below


CREATE FUNCTION GetParentPage
(
@ID int
)
RETURNS int
AS
BEGIN
DECLARE @Root int

;WIth CTE
AS
(SELECT Parent,Child,PageNo
FROM Table
WHERE Parent=@ID
UNION ALL
SELECT t.Parent,t.Child,t.PageNo
FROM CTE c
JOIN Table t
ON t.Parent=c.Child
)
SELECT @Root=PageNo 
FROM CTE
WHERE Child=0
OPTION (MAXRECURSION 0)


RETURN @Root
END


then call it like

SELECT parent,Child,dbo.GetParentPage(parent)
FROM Table

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

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-08-25 : 15:14:45

Run this ...

declare @temp table(id int, parent int, pag int)
insert into @temp values(28, 0, 42)
insert into @temp values(30, 28, 0)
insert into @temp values(31, 30, 0)
insert into @temp values(32, 31, 0)
insert into @temp values(33, 0 ,43)
insert into @temp values(34, 33, 0)
insert into @temp values(35, 34, 0)
insert into @temp values(38, 33, 0)

;with temp as
(select id, parent, pag as page_id from @temp as t1 where parent = 0
union all select t3.id,t3.parent, page_id from @temp as t3 inner join temp as t4 on t4.id = t3.parent)

select * from temp order by id

--- result
id parent page_id
28 0 42
30 28 42
31 30 42
32 31 42
33 0 43
34 33 43
35 34 43
38 33 43

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-26 : 11:35:37

you can make it a derived column ([Page Number] in your example) and then use it like [Page Number]=somevalue

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources