problem in 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 2000 Forums

Transact-SQL (2000)

problem in query

Author

Topic

keyursoni85
Posting Yak Master

233 Posts

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

I have table registration and there are data as below..

RegId RefId Position
1 0
2 1 Left
3 1 Right
4 2 Left
5 3 Right
6 4 Left
7 5 Right
... so on

RegId "1" is default row in table....
Now i want REG ID from table and i have REGID "7" So this ID has Master ID "2" with reference IDs...
HOW CAN i get RegId 2 with the help of id 7
may i need cursor or looping of queries for this?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-26 : 14:31:10

Could you explain how RedId=7 has MasterId=2?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-02-26 : 14:32:28

Am not sure about others..but I really dont understand what you are looking for..can u explain with sameple data amd expected.

what is a Master ID and reference IDs...

i dont see them in ur data

keyursoni85
Posting Yak Master

233 Posts

Posted - 2009-02-26 : 23:39:17

actually in my registration table there are list of registered users and all users have reference id of its upper user... with left or right position...

now in my table below....
RegId RefId Position
1 0
2 1 Left
3 1 Right
4 2 Left
5 3 Right
6 4 Left
7 5 Right

there are list of registrations.. now while i am going to insert new registration i need refid of previous user..

so next entry will be as :- 8 6 Left........

so i need to get RegId - 6 and All you can see one by one 6 has REfId 4 And 4 has Ref Id 2...

so what i want only reference id for next user...and master id

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-26 : 23:57:54

try this
declare @table table(RegId int, RefId int,Position varchar(12))
insert into @table select
1, 0, null union all select
2, 1, 'Left' union all select
3, 1, 'Right' union all select
4,2, 'Left' union all select
5,3, 'Right' union all select
6, 4, 'Left' union all select
7, 5, 'Right'
declare @str varchar(6)
set @str ='left'

insert into @table select max(regid)+1,(select max(regid) from @table where position = @str),@str from @table

select * from @table

keyursoni85
Posting Yak Master

233 Posts

Posted - 2009-02-27 : 02:13:55

yes this is right but i have lots of users..

and in above query REFID=6
but how can we imagine that REFID=6 has master id 2 that i want..

What i want is...
i have name of regid 2 and i am going to insert new user
how can i get ref id ..

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-27 : 02:40:09

see this
declare @table table(RegId int, RefId int,Position varchar(12))
insert into @table select
1, 0, null
declare @str varchar(6)
set @str ='left'

insert into @table select max(regid)+1,ISNULL((select max(regid) from @table where position = @str),1),@str from @table
select * from @table
insert into @table select max(regid)+1,ISNULL((select max(regid) from @table where position = @str),1),@str from @table
select * from @table

keyursoni85
Posting Yak Master

233 Posts

Posted - 2009-02-27 : 04:20:10

thanks for the reply

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-27 : 04:24:47

welcome

did u get the required output r not

keyursoni85
Posting Yak Master

233 Posts

Posted - 2009-02-27 : 06:28:35

no i didn't :(

Actually i am not able to explain properly...

I need to use Binary Tree logic..
See I have lots of users and ref Ids are there..

i need last user for insertion of new user and while i am inserting new user i have master user's name.. so i need to check all users with the help of master user id and find its last user id for new user Reference ID...

E.G. Tree

--------2
----3------4
--5---6---7---8

-- Above is tree and master user is 2
-- While System is going to insert new user it has master id name or master id 2
-- new user be placed at position at Left side of "5"
-- so i need REFID = 5
-- There are LOTS OF USERS with different trees
-- I have to find last user ID for reference for new user and its of master id only

this is my problem
please help..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-27 : 06:37:36

Use a recursive CTE for this.

E 12°55'05.63"
N 56°04'39.26"

keyursoni85
Posting Yak Master

233 Posts

Posted - 2009-02-27 : 07:08:37

Peso...

i didn't getting you.
I have sql 2000 server..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-27 : 07:51:39