Results 1 to 14 of 14

Thread: help on query..

  1. #1
    Join Date
    Dec 2006
    Posts
    8

    help on query..

    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..

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, we will not do homework assignments
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2006
    Posts
    8
    i want to know how it can be done.. not to copy and present the assignment.. pls help me..

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    how can what be done? what is your question?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Dec 2006
    Posts
    8
    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..

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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:

    Code:
    select	itemcode,
    	sum(qty) as purchaseqty
    from	purchasedetails
    group by itemcode
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Dec 2006
    Posts
    8
    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..?

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What platform are you coding, and what school and grade are you in?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Dec 2006
    Posts
    8
    i'm working on postgresql 1.4..
    i'm studying BSc Comp Sci.. 2nd year..

  11. #11
    Join Date
    Dec 2006
    Posts
    8
    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;

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Great. Now you assemble them like Lego Blocks:

    Code:
    select	itemmaster.name,
    	itemmaster.itemcode,
    	PurchaseTotals.purchaseqty,
    	SalesTotals.salesqty
    from	itemmaster
    	left outer join --PurchaseTotals
    		(select	itemcode,
    			sum(qty) as purchaseqty
    		from	purchasedetails
    		group by itemcode) PurchaseTotals
    		on itemmaster.itemcode = PurchaseTotals.itemcode
    	left outer join --SalesTotals
    		(select	itemcode,
    			sum(qty) as salesqty
    		from	salesdetails
    		group by itemcode) SalesTotals
    		on itemmaster.itemcode = SalesTotals.itemcode
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Gee, glad to have done your homework for you.

    Gosh, you are SO welcome!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    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!".

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •