Combining 2 tables in a query

G

gert birkner

I have to combine the data of 2 tables to show them in a list field with 3
columns:
stock table:
CustomerNo.
StockQuantity
if the StockQuantity= 0 there is now row for this customer in this table

order table
CustomerNo
OrderQuantity


CustomerNo is in both tables the primarykey.
I want to show for each CustomerNo the SUM(StockQuantity) and
SUM(OrderQuantity).
The problem is, that neither of these tables contains all of the existing
CustomerNo.
So there are rows in "stock" which don´t exist in "order" and rows in
"order" which don't exist in "stock".
My resultset should look like: (3 columns: CustomerNo; StockQuantity;
OrderQuantity)

100 3 0
101 0 4
102 2 4
....

I can´t formulate a query which show a result like that

thank you in advance

gert birkner
 
T

tomandla

Couldn't he just to a union query to get all of the Customer Nos and
link the two tables to the union query and get all of the information
he is looking for? It seems like that might be easier???
 
G

gert birkner

I tried every form (I was able to think of) to combine thiese 2 tables with
UNION, but I got always a produkt of both tables which made the SUM
senseless.

But I found a solution without a third table.

I wrote an userdefined function in SQL Server (sorry I didn´t say that
first)
.............
RETURN(
SELECT CustomerNo, SUM(StockQuantity), 0 AS OrderQuantity FROM Stock ...
UNION
SELECT CustomerNo, 0 AS StockQuantity, SUM(OrderQuantity) FROM Order ....
)

and used it in my SELECT like
SELECT CustomerNo, SUM(StockQuantity), SUM(OrderQuantity)
FROM dbo.mynewfunction(parameters)
GROUP BY ...
ORDER BY ...

That did it!

Thank you all for your suggestions


gert birkner
 
Top