How to Use SUM function in an SQL Query using Joins among different tables ?

See more:

SQL-Server-2008R2

Hello !

I want to use sum function in my sql query using joins among multiple tables.

Suppose i have three tables.

1:-
tbl_Items
ItemID [int]
ItemName [nvarchar]

2:-
tbl_Orders
OrderID [int]
ItemID [int]
Quantity [money]

3:-
tbl_GatePasses
GatePassID [int]
OrderID [int]
ItemID [int]
Quantity [money]

4:-
tbl_Bills
BillID [int]
GatePassID [int]
OrderID [int]
ItemID [int]
Quantity [money]

the Senior is that some items can be exist in all three tables at the same time and some items in any two tables and some items can be only in one table.

i want to write a query that should give me the following information

1. total quantity of each item in orders
2. total quantity of each item in gate passes
3. total quantity of each item in bills

Special Case:-
if an item exists in only orders table then query should show the sum of quantity in orders and show the 0 quantity in other two tables.

i mean if no record of any item in anyone or any two of the tables then query should tell the sum quantity from the table in which its records exist but show zero for the table in which its record is not exist.

i hope my message is delivered what i want in fact.

i will be grateful to one who will help me.

thanks.