Simple 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)

Simple Query

Author

Topic

ravininave
Posting Yak Master

111 Posts

Posted - 2010-03-29 : 04:53:23

Hi, I'm new in SQL. This is my Table Structure
Code,sCode,eName
1,0,A
2,1,B
3,2,C
4,3,D
5,4,E

Means Code is a sponsor of next code
I've to show output like
Code,eName,sCodeName
Suppose I've to fetch row for code no. 4 then it would show
4,D,C Where C is the eName of Spnsor Code 3.

What would be the Query.

Thanx

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-29 : 05:06:13

IF there is only one level of recursion required then you can do this:



DECLARE @codeData TABLE (
	code INT
	, sCode INT
	, eName CHAR(1)

	PRIMARY KEY (code)
	)

INSERT @codeData (code, sCode, eName)
      SELECT 1, 0, 'A'
UNION SELECT 2, 1, 'B'
UNION SELECT 3, 2, 'C'
UNION SELECT 4, 3, 'D'
UNION SELECT 5, 4, 'E'

-- Show base table
SELECT * FROM @codeData

-- Suppose I've to fetch row for code no. 4 then it would show
-- 4,D,C Where C is the eName of Spnsor Code 3.

SELECT
	cd.code
	, cd.eName
	, cdP.eName
FROM
	@codeData cd
	LEFT JOIN @codeData cdP ON cdP.code = cd.sCode
WHERE
	cd.code = 4

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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-29 : 05:06:18

select
a.Code,
a.eName,
b.eName
from Codetable as a
left join Codetable as b on a.sCode = b.code
where a.code = 4

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-29 : 05:07:23

No, you're never too old to Yak'n'Roll if you're too young to die.

ravininave
Posting Yak Master

111 Posts

Posted - 2010-03-29 : 05:13:12

quote:
Originally posted by webfred

select
a.Code,
a.eName,
b.eName
from Codetable as a
left join Codetable as b on a.sCode = b.code
where a.code = 4

No, you're never too old to Yak'n'Roll if you're too young to die.

Yeh, it was so easy. Thanx dude. It solves my prob. Thanx Again.

VB6/ASP.NET
------------------------
http://www.nehasoftec.com

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-03-29 : 05:16:42

You're welcome.
But my solution is exactly the same to Charlie's solution.
The only difference is that Charlie has provided a solution with using sample data.

No, you're never too old to Yak'n'Roll if you're too young to die.

ravininave
Posting Yak Master

111 Posts

Posted - 2010-03-29 : 05:21:14

yaa, thanx both of u

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-29 : 05:25:55

quote:
Originally posted by webfred

You're welcome.
But my solution is exactly the same to Charlie's solution.
The only difference is that Charlie has provided a solution with using sample data.

No, you're never too old to Yak'n'Roll if you're too young to die.

Cheers Dude!

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources