nested help - 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)

nested help

Author

Topic

artbishop
Starting Member

18 Posts

Posted - 2009-02-18 : 22:14:31

How do i get the following result:

RANKID NAMEID NAME
0 1 Alfred
1 2 Jen
2 5 Amber
2 3 Maricel
1 4 Philip

if I have two SQL database table:

first table is dbo.NAME

ID NAME
1 Alfred
2 Jen
3 Maricel
4 Philip
5 Amber

then second table is RANK

NAMEID RANKID
1 0
2 1
3 2
4 1
5 2

thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-18 : 22:17:26

Learn JOINS in SQL Server. Can we see what you tried?

artbishop
Starting Member

18 Posts

Posted - 2009-02-18 : 22:21:44

SELECT TOP 2 dbo.T_Relationships.PARENT_NAMEID AS LEVEL, dbo.T_Relationships.NAMEID, dbo.T_Names.NAME
FROM dbo.T_Names LEFT JOIN
dbo.T_Relationships ON dbo.T_Names.ID = dbo.T_Relationships.NAMEID
UNION ALL
SELECT TOP 2 dbo.T_Relationships.PARENT_NAMEID AS LEVEL, dbo.T_Relationships.NAMEID, dbo.T_Names.NAME
FROM dbo.T_Names LEFT JOIN
dbo.T_Relationships ON dbo.T_Names.ID = dbo.T_Relationships.NAMEID
WHERE dbo.T_Relationships.NAMEID = 5
UNION ALL
SELECT TOP 1 dbo.T_Relationships.PARENT_NAMEID AS LEVEL, dbo.T_Relationships.NAMEID, dbo.T_Names.NAME
FROM dbo.T_Names LEFT JOIN
dbo.T_Relationships ON dbo.T_Names.ID = dbo.T_Relationships.NAMEID
WHERE dbo.T_Relationships.PARENT_NAMEID = 2
UNION ALL
SELECT TOP 1 dbo.T_Relationships.PARENT_NAMEID AS LEVEL, dbo.T_Relationships.NAMEID, dbo.T_Names.NAME
FROM dbo.T_Names LEFT JOIN
dbo.T_Relationships ON dbo.T_Names.ID = dbo.T_Relationships.NAMEID
WHERE dbo.T_Relationships.NAMEID = 4

X002548
Not Just a Number

15586 Posts

Posted - 2009-02-18 : 22:21:52

SELECT * FROM Name Join Rank ON Name.ID = Rank.NameId

?????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-18 : 22:24:45

quote:
Originally posted by artbishop

SELECT TOP 2 dbo.T_Relationships.PARENT_NAMEID AS LEVEL, dbo.T_Relationships.NAMEID, dbo.T_Names.NAME
FROM dbo.T_Names LEFT JOIN
dbo.T_Relationships ON dbo.T_Names.ID = dbo.T_Relationships.NAMEID
UNION ALL
SELECT TOP 2 dbo.T_Relationships.PARENT_NAMEID AS LEVEL, dbo.T_Relationships.NAMEID, dbo.T_Names.NAME
FROM dbo.T_Names LEFT JOIN
dbo.T_Relationships ON dbo.T_Names.ID = dbo.T_Relationships.NAMEID
WHERE dbo.T_Relationships.NAMEID = 5
UNION ALL
SELECT TOP 1 dbo.T_Relationships.PARENT_NAMEID AS LEVEL, dbo.T_Relationships.NAMEID, dbo.T_Names.NAME
FROM dbo.T_Names LEFT JOIN
dbo.T_Relationships ON dbo.T_Names.ID = dbo.T_Relationships.NAMEID
WHERE dbo.T_Relationships.PARENT_NAMEID = 2
UNION ALL
SELECT TOP 1 dbo.T_Relationships.PARENT_NAMEID AS LEVEL, dbo.T_Relationships.NAMEID, dbo.T_Names.NAME
FROM dbo.T_Names LEFT JOIN
dbo.T_Relationships ON dbo.T_Names.ID = dbo.T_Relationships.NAMEID
WHERE dbo.T_Relationships.NAMEID = 4

OMG

what is this?

X002548
Not Just a Number

15586 Posts

Posted - 2009-02-18 : 22:29:57

WOW

It's still Wednesday isn't it?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-18 : 22:38:05

Go through this:
http://www.w3schools.com/sql/default.asp

artbishop
Starting Member

18 Posts

Posted - 2009-02-18 : 22:45:02

I am having problem arranging the rankid from 0 1 2 2 1...wherein the first number 2 should be Amber instead of Maricel...

RANKID NAMEID NAME
0 1 Alfred
1 2 Jen
2 5 Amber
2 3 Maricel
1 4 Philip

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-18 : 23:08:15

[code]Select RankID,NAMEID,[Name]
from(Select
ROW_NUMBER() Over(partition by RankID order by NAME)as seq,M.RankID,T.NAMEID,T.[Name] from Table1 T
inner join Table2 M
On M.id = T.id)Z
order by seq[/code]

artbishop
Starting Member

18 Posts

Posted - 2009-02-18 : 23:41:11

I followed the formula:

SELECT dbo.T_Relationships.PARENT_NAMEID, dbo.T_Relationships.NAMEID, dbo.T_Names.NAME
FROM (SELECT ROW_NUMBER() OVER (partition BY dbo.T_Relationships.PARENT_NAMEID
ORDER BY NAME) AS seq, dbo.T_Relationships.PARENT_NAMEID, dbo.T_Relationships.NAMEID, dbo.T_Names.NAME
FROM dbo.T_Names INNER JOIN
dbo.T_Relationships ON dbo.T_Names.ID = dbo.T_Relationships.NAMEID) Z
ORDER BY seq

now i am getting the error message: [Microsoft\[ODBC SQL Server Driver][SQL SERVER]'ROW_NUMBER' is not a recognized function name...

any idea?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-18 : 23:45:39

r u using 2000 r 2005 ?

artbishop
Starting Member

18 Posts

Posted - 2009-02-18 : 23:49:02

2000

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-18 : 23:51:51

in 2000 u can't use the row_number function

artbishop
Starting Member

18 Posts

Posted - 2009-02-18 : 23:53:07

any idea to solve the problem?

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-19 : 00:37:28

[code]

declare @Name Table ( ID int, NAME varchar(32))
insert into @name
select 1, 'Alfred' union all
select 2 ,'Jen' union all
select 3 ,'Maricel' union all
select 4 ,'Philip' union all
select 5 ,'Amber'

declare @rank table (NAMEID int, RANKID int)
insert into @rank
select 1, 0 union all
select 2, 1 union all
select 3, 2 union all
select 4, 1 union all
select 5, 2

declare @cnt Int
select @cnt = count(*) from @name

select r.rankid,n.id,n.name
from @name n
inner join @rank r on r.nameid = n.id
order by case when id = (select max(id) from @name) then id-((@cnt/2)+1) else id end

[/code]

artbishop
Starting Member

18 Posts

Posted - 2009-02-19 : 00:59:32

thanks.. =)

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-19 : 01:04:42

Welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources