queries ??? help pls - 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

General SQL Server Forums

New to SQL Server Programming

queries ??? help pls

Author

Topic

arul
Starting Member

3 Posts

Posted - 2008-02-25 : 00:50:43

I have a single table "amount" which has only three columns such as memberid referenceid and AMOUNT with following data ........
memberid referenceid AMOUNT
L001 L001
L002 L001
L003 L002
L004 L003
and so on
my needed query willbe if i choose L003 from referenceid the query should select the previous column where it was a memberid and again it choose the same row refernceid of(L003) as L002 where L002 was a memberid and choose L002s referenceid which is L001(BOTTOM TO UP ZIGZAG MODEL)
Finally i should REACH the memberid(L001) and
the other one i need is i should return the count of no of rows crossed above,
p.s the order in data will be random and not from down to upwards.
and forgive my grammar

ranganath
Posting Yak Master

209 Posts

Posted - 2008-02-25 : 02:20:15

Hi,

Try This

Declare @Temp Table (Id varchar(100), RefId varchar(100), Amt Decimal(18,3))
Insert Into @Temp
Select 'L001', 'L000', 100 Union All
Select 'L002', 'L001', 200 Union All
Select 'L003', 'L002', 300 Union All
Select 'L004', 'L003', 400

Declare @RefId varchar(100)
Select @RefId = 'L003'
; with Ref(Id, RefId, Amt, HLevel, cnt)
As
(
Select Id, RefId, Amt, 0 as Hlevel, 1 From @Temp where Id = @RefId
Union All
Select T.Id, T.RefId, T.Amt, Hlevel+1, cnt +1 From @Temp T
Inner join Ref R on R.RefId = T.Id
)
Select Id, RefId, Amt, HLevel , cnt From Ref

it will work only in 2005.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-25 : 02:44:29

[code]Create table mem
(memberid varchar(10),
referenceid varchar(10)
)
INSERT INTO mem values ('L001','L001')
INSERT INTO mem values ('L002', 'L001')
INSERT INTO mem values ('L003', 'L002')
INSERT INTO mem values ('L004', 'L003' )
;

WIth CTE(MID,RID) AS
(
SELECT memberid,referenceid
FROM mem WHERE referenceid=@RefID

UNION ALL

SELECT memberid,referenceid
FROM mem m
INNER JOIN CTE c
ON c.RID=m.memberid
AND c.RID<>c.MID
)
select * from CTE Order BY 1
output
---------------------
MID RID
---------- ----------
L001 L001
L002 L001
L003 L002
L004 L003[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-25 : 10:06:23

Where is the number of rows as requested?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-25 : 10:31:01