Required a 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

General SQL Server Forums

New to SQL Server Programming

Required a simple query

Author

Topic

zafarthesultan
Starting Member

16 Posts

Posted - 2010-01-26 : 00:45:02

Can anyone help me to get the result please?

IF OBJECT_ID('TempDB..#mastertable','U') IS NOT NULL
DROP TABLE #mastertable;

CREATE TABLE #mastertable
(
ID int,
Introducer int,
MemberName nvarchar(50),
LeftMember int,
RightMember int,
BusinessValue int
)

INSERT INTO #mastertable
(ID,Introducer,MemberName,LeftMember,RightMember,BusinessValue)
SELECT 1,10, 'A', 2,3, 50 UNION ALL
SELECT 2,10, 'B', 4,5, 50 UNION ALL
SELECT 3,1, 'C', 6,7, 50 UNION ALL
SELECT 4,1, 'D', NULL,NULL, 50 UNION ALL
SELECT 5,2, 'E', NULL,NULL, 50 UNION ALL
SELECT 6,3, 'F', NULL,NULL, 50 UNION ALL
SELECT 7,4, 'G', NULL,NULL, 50
;

IF OBJECT_ID('TempDB..#valuetable','U') IS NOT NULL
DROP TABLE #valuetable;

CREATE TABLE #valuetable
(
ID int,
valuepoints int
)

INSERT INTO #valuetable
(ID,valuepoints)
SELECT 1,100 UNION ALL
SELECT 2,100 UNION ALL
SELECT 3,100 UNION ALL
SELECT 4,0 UNION ALL
SELECT 5,0 UNION ALL
SELECT 6,0 UNION ALL
SELECT 7,0
;

1 has 2 to his left and 3 to his right. 2's value points are 100 and 3's value points are 100. So the points of 1 will be calculated as follows:
1's left member in the mastertable is 2 so 1's leftpoints will be points of 2 in the valuetable i.e. 100. Similarly 1's right member in the master table is 3 so rightpoints of 1 will be points of 3 in the valuetable i.e. 100

The output required:

id leftpoints rightpoints

1 100 100
2 0 0
3 0 0
4 0 0
5 0 0
6 0 0
7 0 0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-26 : 00:57:04

There are several examples in the SQL Server help file (Books Online) about recursive CTE's.

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-26 : 01:32:18

i dont think you're asking for recursive query here. so i think below may be sufficient for you


select m.ID,vl.valuepoints as leftpoints,
vr.valuepoints as rightpoints
from #mastertable m
inner join #valuetable vl
on vl.id= m.LeftMember
inner join #valuetable vr
on vr.id= m.RightMember

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources