Combining resultsets

[access97to2000]

Hi,
Here is my problem.
Table A:
Productid , Name, Cost
1 abc 3
2 fsd 4
3 fdsf 15

Table B
Productid inventory
1 1
1 5
2 3
3 8
2 6


Table C
PRoductid unitssold
1 5
2 8
1 4
3 3
3 19


I want the output as

ProductID Name Standardcost Inventory unitssold(sum)
1 abc 3 6 9
2 fsd 4 9 8
3 fdsf 15 8 22

Please help me out

 

[mrdenny]

You need a standard join.

select a.productid,
   a.name,
   a.cost,
   b.inventory,
   sum(c.unitssold)
from [table a] a
join [table b] b on a.productid = b.productid
join [table c] c on a.productid = c.productid

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[access97to2000]

Thanks for the reply, but
it gives me a wrong result set.....
it doesnt givve the exaact sum(unitssold),sum(inventory)

 

[JamesLean]

Try this:

SELECT productid,
  name,
  cost,
  (SELECT SUM(inventory) FROM tableB WHERE productid = a.productid) AS inventory,
  (SELECT SUM(unitssold) FROM tableC WHERE productid = a.productid) AS unitssold
FROM tableA a

--James

 

[access97to2000]

worked like magic - Thank you very much