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
gorexyAsked:
Who is Participating?
 
sdstuberCommented:
I believe both of my answers are correct.
0
 
sdstuberCommented:
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
0
 
gorexyAuthor Commented:
hm..but how does this relate to the original table? i.e. TABLE PRODUCT
0
 
sdstuberCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.