Click here to Skip to main content
15,037,928 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Posted

1 solution

Use a JOIN.
I refer you to your question on that subject: Difference between Inner And Outer Join[^]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900