Retrieve Data From Multiple Tables in a Single Query !

See more:

SQL-Server-2008R2

Hi !

I am working on a database in SQL Server 2008.
In my database i have three tables named "tbl_Orders, tbl_GatePasses, tbl_Bills" respectively.

An other table named "tbl_Items" contains the items list.

The fields of all four tables are below.

Table1 (tbl_Items)

Field1 ----- ItemID (Int)
Field2 ----- ItemName (Nvarchar)
Field3 ----- ItemSalePrice (Money)


Table2 (tbl_Orders)

Field1 ----- OrderID (nvarchar)
Field2 ----- SrNo (int)
Field3 ----- ItemID (Int)
Field4 ----- Quantity (Bigint)


Table3 (tbl_GatePasses)

Field1 ----- GatePassNo (int)
Field2 ----- OrderID (nvarchar)
Field3 ----- SrNo (int)
Field4 ----- ItemID (Int)
Field5 ----- Quantity (Bigint)


Table4 (tbl_Bills)

Field1 ----- BillNo (int)
Field2 ----- GatePassNo (int)
Field3 ----- OrderID (nvarchar)
Field4 ----- SrNo (int)
Field5 ----- ItemID (Int)
Field6 ----- Quantity (Bigint)

now i want to right a query which gives the information for an item that how many quantity is in tbl_Orders, tbl_GatePasses and tbl_Bill separately.
for the above purpose i write the following query

SELECT
tbl_Items.ItemName,
SUM(tbl_Orders.Quantity) As 'QtyInOrders',
SUM(tbl_Gatepasses.Quantity) As 'QtyInGatePasses',
SUM(tbl_Bills.Quantity) As 'QtyInBills'
FROM
tbl_Items, tbl_Orders, tbl_GatePasses, tbl_Bills
WHERE
tbl_Items.ItemID=125 AND
tbl_Items.ItemID=tbl_Orders.ItemID AND
tbl_Items.ItemID=tbl_GatePasses.ItemID AND
tbl_Items.ItemID=tbl_Bills.ItemID
GROUP BY
tbl_Items.ItemName

now this query gives me right information but if and only if when the specified item exists in all tables but if an items exists in two tables i.e. tbl_Orders and tbl_GatePasses but does not exist in third table tbl_Bills then the where clause becomes false and the whole query returns null value but in fact the item exists in two tables and i want to see the quantity from these two tables and in which table item does not exist then query should leave that table.

Hopefully i have delivered my problem and anyone can understand it.
Please help me in my problem and tell me what to do ?

Thanks so much.

Regards:
Asim Mughal