Solved

Purchase Order System: Converting complex subqueries into join statements for mySQL 4.0

Posted on 2004-08-26
3
334 Views
Last Modified: 2010-05-18
This is fairly involved so I'll do my best to first explain my project.

I am working on a PHP/mySQL purchase order system and want to be able to create some purchase order status reports. There are four main tables that I need to use in determining the status and displaying the results. I'll list the tables and show the fields used for this problem.

tbl_PurchaseOrder
-----------------------------------
PurchaseOrderID
PurchaseOrder_StoreID
PurchaseOrder_CustomerID
PurchaseOrderOrderDate
PurchaseOrderScheduleDate
PurchaseOrderCompletionDate


tbl_Customer
-----------------------------------
CustomerID
CustomerFirstName
CustomerLastName
CustomerPrimaryPhone


tbl_PurchaseOrderItem
-----------------------------------
PurchaseOrderItemID
PurchaseOrderItem_PurchaseOrderID
PurchaseOrderItem_ItemID
PurchaseOrderItemAckNumber
PurchaseOrderItemReceivedDate


tbl_Item
-----------------------------------
ItemID
ItemTotalParts      //Each item can consist of up to 4 parts, but we are not tracking at that level. However, we will need to display the total parts for an order later.


This is a basic PO system where a customer's purchase order can have many line items. These POs are completed by a sales associate at the store and then later submitted to the manufacturer. The manufacturer then acknowledges the receipt of the order and will subsequently provide an acknowledgement number. Because the manufacturer has the option of acknowledging only part of a purchase order, each line item in the tbl_PurchaseOrderItem table will receive an acknowledgement number (PurchaseOrderItemAckNumber). Likewise, the manufacturer may only ship part of a purchase order to the store. Therefore, each line item will also have its own PurchaseOrderItemReceiveDate.

The store will wait until an entire order is received from the manufacturer to schedule a delivery date with the customer (tbl_PurchaseOrder.PurchaseOrderScheduledDate) and also to deliver to the customer (tbl_PurchaseOrder.PurchaseOrderCompletionDate).

Having said that, I will now explain the status reports I am looking to build. A purchase order will cycle through four statuses. The first and last status reports are working fine.

The problem I am having is with the two intermediary status reports. The second report is called the Ordered Status Report. This will display purchase orders where EVERY line item in the purchase order has an acknowledgement date. I have this subquery statement that I believe works:

      SELECT DISTINCT tbl_PurchaseOrder.*, tbl_Customer.*, SUM(ItemTotalParts) AS TotalParts
      FROM (tbl_Customer INNER JOIN tbl_PurchaseOrder ON tbl_Customer.CustomerID = tbl_PurchaseOrder.PurchaseOrder_CustomerID) INNER JOIN tbl_PurchaseOrderItem ON tbl_PurchaseOrder.PurchaseOrderID = tbl_PurchaseOrderItem.PurchaseOrderItem_PurchaseOrderID
      WHERE tbl_PurchaseOrderItem.PurchaseOrderItemAckNumber IS NOT NULL AND tbl_PurchaseOrderItem.PurchaseOrderItemReceivedDate IS NULL AND tbl_PurchaseOrder.PurchaseOrderID NOT IN
      (SELECT DISTINCT tbl_PurchaseOrder.PurchaseOrderID FROM (tbl_Customer INNER JOIN tbl_PurchaseOrder ON tbl_Customer.CustomerID = tbl_PurchaseOrder.PurchaseOrder_CustomerID) INNER JOIN tbl_PurchaseOrderItem ON tbl_PurchaseOrder.PurchaseOrderID = tbl_PurchaseOrderItem.PurchaseOrderItem_PurchaseOrderID
      WHERE tbl_PurchaseOrderItem.PurchaseOrderItemAckNumber IS NULL)
      ORDER BY tbl_PurchaseOrder.PurchaseOrderDate;

I included the NOT IN clause so it will exclude any purchase order that might have line items with acknowledgment numbers AND a line item without an acknowledgement number. Of course the problem lies in the fact that our hosting company is running mySQL 4.0 and subqueries are not allowed. I need to therefore convert this to some type of join statement. All of the examples I have seen so far a fairly simple and do not initially use joins in the FROM clause.

The third report is the Received Status Report. This will include all purchase orders where ALL of their line items have a PurchaseOrderItemReceiveDate. Again, this will need to exclude all purchase orders that contain ANY line items where either the PurchaseOrderItemAckNumber or PurchaseOrderItemReceiveDate or both are NULL. I have this subquery that I believe works (I don’t have way of testing it):

      SELECT DISTINCT tbl_PurchaseOrder.*, tbl_Customer.*
      FROM (tbl_Customer INNER JOIN tbl_PurchaseOrder ON tbl_Customer.CustomerID = tbl_PurchaseOrder.PurchaseOrder_CustomerID) INNER JOIN tbl_PurchaseOrderItem ON tbl_PurchaseOrder.PurchaseOrderID = tbl_PurchaseOrderItem.PurchaseOrderItem_PurchaseOrderID
      WHERE tbl_PurchaseOrder.PurchaseOrderCompletionDate IS NULL AND tbl_PurchaseOrder.PurchaseOrderID NOT IN
      (SELECT DISTINCT tbl_PurchaseOrder.PurchaseOrderID FROM (tbl_Customer INNER JOIN tbl_PurchaseOrder ON tbl_Customer.CustomerID = tbl_PurchaseOrder.PurchaseOrder_CustomerID) INNER JOIN tbl_PurchaseOrderItem ON tbl_PurchaseOrder.PurchaseOrderID = tbl_PurchaseOrderItem.PurchaseOrderItem_PurchaseOrderID
      WHERE tbl_PurchaseOrderItem.PurchaseOrderItemAckNumber IS NULL OR tbl_PurchaseOrderItem.PurchaseOrderItemReceivedDate IS NULL)
      ORDER BY tbl_PurchaseOrder.PurchaseOrderDate;

If anyone has a way of converting these two subqueries to join statements it would be greatly appreciated. Or if there is an easier way of doing these, please let me know.
0
Comment
Question by:ogicu812
3 Comments
 
LVL 5

Accepted Solution

by:
hkamal earned 400 total points
ID: 11911173
ogicu812,
My initial thought would be to use a temp table and join that in your report query. Something like:

SELECT DISTINCT PurchaseOrderItem_PurchaseOrderID
INTO #CompleteOrders
FROM tbl_PurchaseOrderItem
WHERE PurchaseOrderItemAckNumber IS NOT NULL

DELETE #CompleteOrders
FROM #CompleteOrders c, tbl_PurchaseOrderItem p
WHERE c.PurchaseOrderItem_PurchaseOrderID =p.PurchaseOrderItem_PurchaseOrderID
AND p.PurchaseOrderItemAckNumber IS NULL

Now you can use #CompleteOrders as a "clean" list of orders whos line items have ALL been acknowledged

You can apply similar logic to your other query

With MySQL 4.1 you won't haved to worry about this as it will have support for Subqueries ..

0
 

Author Comment

by:ogicu812
ID: 11916652
hkamal,

Thanks a ton!

I ended up creating a temp table for each status report and used some INSERT statements instead of the SELECT INTO statements. It works great though. I thought I read that the SELECT INTO statement puts values directly in to variables instead of a table.
0
 
LVL 2

Expert Comment

by:nexusSam
ID: 11916880
You're welcome,
Actually, SELECT INTO is the same as INSERT SELECT except that the former is not logged and therefore quicker. Also, since you're not defining the columns explicitly, mySQL makes a guess about data types. Usually that's not a problem but it's something to be aware of

H
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Creating and Managing Databases with phpMyAdmin in cPanel.
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question