i got an assignment on sql.. but i cant complete the query.. i'm working on it from yesterday.. pls help me..

Create tables with corresponding fileds as described below

itermaster
itemcode
name
price

party master
partycode(PK)
name

purchase master
date
partycode(fk)
PO Number


purchase details
PO Number
itemcode(FK)
qty


sales master
date
partycode(FK)
Sales Number


Sales details
Sales Number
item code(FK)
qty



Generate reports using SQL which contains
name,price,Tot. Purchased,Total Sales,Balance
and

Name,Item,Purchase,Sales


Some example datas are given below for reference.




item master

itemcode(pk) name price
1 Lux 12
2 Cinthol 16
3 Colgate 18
4 Ponds 10
5 Siscor 20




party master
partycode(PK) name
1 Jaya Agencies
2 Buraaq
3 Mascot
4 N R Trading Co.
5 Meridian
6 V.S Agencies
7 RMPL
8 VV
9 ChaPuPu



purchase master
date partycode(fk) PO Number
01/07/06 4 P001
05/07/06 2 P002
06/07/06 3 P003


purchase details
PO Number itemcode(FK) qty
P001 1 5
P001 2 10
P001 4 7
P001 5 10
P002 4 15
P003 1 10



sales master
date partycode(FK) Sales Number
07/07/06 6 s001
08/07/06 8 s002

Sales details
Sales Number item code(FK) qty
s001 2 1
s002 4 4
s001 2 2
s002 1 3
s002 1 1



Generate reports using SQL which contains
name,price,Tot. Purchased,Total Sales,Balance
and

Name,Item,Purchase,Sales

Query 1.

code name price Tot. Purchased Total Sales Balance
1 Lux 12 10 4 6
2 Cinthol 16
3 Colgate 18
4 Ponds 10
5 Siscor 20 10 0 10



Query 2.
Code Name Item Purchase Sales
1 Jaya Agencies
2 Buraaq Cinthol 40 0
Colgate
Ponds
3 Mascot
4 N R Trading Co.
5 Anees
6 Neeethu
7 Anitha
8 George
9 Ranjusha

thanks in advance..

sorry, we will not do homework assignments

i want to know how it can be done.. not to copy and present the assignment.. pls help me..

how can what be done? what is your question?

its given in my post.. it is actuallly to join three tables which adds values from two tables and display all the records even if it is null.. using left join we can do this..

i wrote this query:

select im.itemcode, im.name, im.price, sum(pd.qty), sum(sd.qty) from itemmaster im
left join
purchasedetails pd on im.itemcode = pd.itemcode
left join
salesdetails sd on im.itemcode = sd.itemcode
group by im.itemcode, im.name, im.price;

but the sum value is getting doubled..

Imagine that for every itemmaster record there exists two purchasedetails records and two salesdetails records. When the table are joined in the manner you have used, this results in four possible combinations of the purchase and sales records. Each purchase record appears twice in the resultset, as does each sales records, and so when you aggregate the results you end up doubling the summary value.

One solution is to use a nested subquery to calculate purchase totals and sales totals independently:

gives you the total purchase quantity for each item. Now write the same query for salesdetails and look up the syntax for joining these to the itemmaster table as subqueries.

i got the query..

select im.itemcode,
im.name,
im.price,
purchaseqty,
salesqty,
(purchaseqty-salesqty) as balance
from itemmaster im
left join
(select purch.itemcode as code,
purchaseqty,
salesqty
from (select itemcode, sum(qty) as purchaseqty from purchasedetails group by itemcode) as purch
left join
(select itemcode, sum(qty) as salesqty from salesdetails group by itemcode) as sales
on purch.itemcode=sales.itemcode) as foo
on im.itemcode=foo.code

but its not displaying the difference in balance column if salesqty is null.. any idea how to do..?

thanks for all the help u've given..

and can we do this same query eliminating subqueries and making it a simple query..?

Ok, you're trying, so I'll keep helping you.

Scrap the code you just did. It was a noble effort, but you need to change your way of thinking.

Write three querys for me:
1) A query to return just the ItemMaster data
2) A query to return the quantity purchased for each itemcode (Hey! I already did this one for you! )
3) A query to return the quantity sold for each itemcode

Post these three statements to the forum.

What platform are you coding, and what school and grade are you in?

i'm working on postgresql 1.4..
i'm studying BSc Comp Sci.. 2nd year..

dear blindman,

here's the queries u asked..

1) select name,
itemcode
from itemmaster;

2) select itemcode,
sum(qty) as purchaseqty
from purchasedetails
group by itemcode;

3) select itemcode,
sum(qty) as salesqty
from salesdetails
group by itemcode;

Great. Now you assemble them like Lego Blocks:

Gee, glad to have done your homework for you.

Gosh, you are SO welcome!

Do I need to buy a new spectacles, or is a post #12 3/4 really missing?

I just hope that ronysamuel didn't die of happiness; much better scenario is that he simply forgot to say "thank you!".