Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2010
    Posts
    1

    Unanswered: Pls Help to Get Result..

    Hi Everyone..

    I have 3 Tables


    First is Prodm
    ---------------------------
    PdID| Name |OPStock
    ----------------------------
    1 | aaaa | 10.00
    ----------------------------
    2 | bbbb | 20.00
    ----------------------------
    3 | cccc | 50.00
    ----------------------------
    4 | dddd | 30.00
    ----------------------------
    5 | eeee | 40.00
    ----------------------------


    Second Is Production
    ---------------------
    PdId |Date |Production
    -------------------------------
    2 |01/01/2010|100.00
    -------------------------------
    5 |01/01/2010|200.00
    -------------------------------
    1 |01/01/2010|100.00
    -------------------------------

    Third IS Sales
    -------------
    PdID|Date | Sales
    ----------------------
    1 |01/01/2010| 5.00
    ----------------------
    1 |01/02/2010|10.00
    ----------------------
    3 |01/02/2010|50.00
    ----------------------




    Now SQL Should Display Result as under..

    PdId |Name|OpStk|ProdQ|Sales|CLStock

    0001|aaaa |10.00|100.00|15.00|095.00
    0002|bbbb |20.00|100.00|00.00|120.00
    0003|cccc |50.00|000.00|50.00|000.00
    0004|dddd |30.00|000.00|30.00|000.00
    0005|eeee |40.00|200.00|00.00|240.00



    Hope I will get help from u all..

    Rajesh

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT m.PdId 
         , m.Name
         , m.OpStk
         , COALESCE(p.ProdQ,0) AS ProdQ
         , COALESCE(s.Sales,0) AS Sales
         , COALESCE(p.ProdQ,0) -
           COALESCE(s.Sales,0) AS CLStock
      FROM Prodm  AS m
    LEFT OUTER
      JOIN Production AS p
        ON p.PdId = m.PdId
    LEFT OUTER
      JOIN ( SELECT PdId
                  , SUM(Sales) AS Sales
               FROM Sales
             GROUP
                 BY PdId ) AS s
        ON s.PdId = m.PdId
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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