SQL QUERY

[NAI]

Table A has Field 1 as a reference, & Field 2 as a VALUE.
Table B has the same Field 1 reference, with a "Link Reference" in Field 2.
I need to write a Stored Procedure which accepts Field 1 as a run-time variable, and returns the total VALUE, including all linked accounts.
ie Field 1 Field 2
TABLE A 12 20
13 30
14 40

TABLE B 12 13
12 14

The Stored Procedure would be run with "12" as a variable, &
would return an answer of 90 (20 + 30 + 40).
This is probably dead-easy, but I am new to this game !
I would prefer not to use a VIEW.
We use SYBASE.
Please help !
+

 

[rac2]

Hello Nai,

CREATE PROCEDURE proc_linked_account_total(
@base_account INT
)
AS

SELECT SUM(tableA.field2) AS "Total"
FROM tableA
WHERE tableA.field1 = @base_account

OR

tableA.field1 IN
(SELECT tableB.field2 FROM tableB
WHERE tableB.field1 = @base_account)

This is where I would start in MS SQL Server.

Maybe this will give you some ideas for Sybase.

Richard

 

[tlbroadbent]

Try something like this.

Select
a1.Field1,
sum(a1.field2) + sum(a2.field2) As TotAmt
From TableA As a1
Inner Join TableB As b
On a1.field1=b.field1
Inner Join TableA As a2
On b.field2=a2.field1
Where a1.field1<>a2.field1 Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.