asked on

MS SQL Stored Procedure from different tables

HI,
I am new in using Stored Procedure and would like to boost the speed of my code.
I got a table like
TABLE Product
1. Q123
2. Q234
3. H456
4. H653

and for each item it will refer to anohter table
Table A
1. Q123 4 5 6
2. Q234 5 1 2
3. Q123 45 33 44
4. Q123 190 33 21

Table B
1. H456 5443
2. H456 43
3. H653 33

I would like to sum the value of TABLE PRODUCT so that the final table will store all the item with subtotal of each from Table A and Table B and the final reuslt should be ordered from large sum to lowesr (i.e. descending order)

Result
1. Q123 197130 --------- (4x5x6+45x33x44+190x33x21)
2. Q234 10 ----------(5x1x2)

etc..

is it possible to use stored procedure to do so ? So that I can use this table often later

Sean Stuber

assuming "A" looks like (id, x, y, z) and "B" looks like (id, x) then

SELECT ID, SUM(x * y * z)
FROM a
GROUP BY ID
UNION
SELECT ID, x
FROM b
ORDER BY 2 DESC, 1

gorexy

ASKER

hm..but how does this relate to the original table? i.e. TABLE PRODUCT

Sean Stuber

it doesn't, but it doesn't have to.

if you really want it to then. simply join it

SELECT p.ID, x.RESULT
FROM product p,
(SELECT ID, SUM(x * y * z) RESULT
FROM a
GROUP BY ID
UNION
SELECT ID, x RESULT
FROM b) x
WHERE p.ID = x.ID
ORDER BY RESULT DESC, ID

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer