15,122,559 members
See more:
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.
Posted
Kornfeld Eliyahu Peter 8-Sep-15 3:17am

Do you have the query to list your requirements WITHOUT the SUMmary?
(or in other words: What have you tried?[^])
Peter Leow 8-Sep-15 3:35am

I think you have to get the house (database) in order first.
1. How is orderid related to gatepassid? Are they one to one or many (orderid) to one (gatepassid)?
2. If you can derive orderid/s (multiple?) from a gatepassid, why repeat itemid and quantity in tbl_gatepasses?
3. Same query and consideration for tbl_bills.

## Solution 1

It doesn't quite work like that.
We do not do your work for you.
If you want someone to write your code, you have to pay - I suggest you go to Freelancer.com and ask there.

But be aware: you get what you pay for. Pay peanuts, get monkeys.