Results 1 to 3 of 3

Thread: SQL help

  1. #1
    Join Date
    Aug 2012
    Posts
    6

    SQL help

    Hi there.
    I would very much appreciate any help you can give me on a SQL statement I wish to run.


    I have 3 tables;
    • AC_CODE_MATERIAL_SUPPLIERS (shopping lists)
    • AC_PURCHASE_ORDER_HEADERS
    • AC_PURCHASE_ORDER_DETAILS

    The AC_CODE_MATERIAL_SUPPLIERS file contains a Shopping List for all Materials that have ever been purchased from Suppliers.

    The AC_PURCHASE_ORDER_HEADERS table contains all Purchase Orders to Suppliers.

    The AC_PURCHASE_ORDER_DETAILS table contains all Materials that were ordered on the Purchase Orders.

    The AC_PURCHASE_ORDER_HEADERS and AC_PURCHASE_ORDER_DETAILS tables are linked by PURCHASE_ORDER_NO

    I wish to delete Materials from a Suppliers Shopping list (where there were no Materials ordered since 2006.

    I am using the following SQL statement to see when the last order for the Materials was;

    "
    Select h.supplier_code, d.material_code, MAX(H.PURCHASE_ORDER_DATE)

    From ac_purchase_order_headers h,
    ac_purchase_order_details d
    where
    (h.purchase_order_no = d.purchase_order_no)
    GROUP BY
    D.MATERIAL_CODE,
    H.SUPPLIER_CODE

    HAVING MAX(H.PURCHASE_ORDER_DATE) < '01-JAN-2007' "



    So in summary, I am attempting to delete all records in AC_CODE_MATERIAL_SUPPLIERS table where the Supplier (SUPPLIER_CODE) and Materials(MATERIAL_CODE) exists in the list that the above SQL produces.


    I think it will go something like this ;
    'SELECT * FROM AC_CODE_MATERIAL_SUPPLIERS where not exists
    ........................ ? '

    Unfortunately after that I get stuck

    Thank you very much in advance.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    DELETE
      FROM ac_code_material_suppliers 
     WHERE EXISTS
           ( SELECT 1
               FROM ( SELECT h.supplier_code
                           , d.material_code
                        FROM ac_purchase_order_headers h
                      INNER
                        JOIN ac_purchase_order_details d
                          ON d.purchase_order_no = h.purchase_order_no
                      GROUP 
                          BY h.supplier_code
                           , d.material_code
                      HAVING MAX(h.purchase_order_date) < '01-JAN-2007'
                    ) AS t
              WHERE t.supplier_code = ac_code_material_suppliers.supplier_code
                AND t.material_code = ac_code_material_suppliers.material_code  
           )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2012
    Posts
    6
    Thank you, thank you, thank you - your help is very much apprecaited

Posting Permissions

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