Solved

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

Posted on 2004-08-26
3
330 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

743 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now