?
Solved

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

Posted on 2004-08-26
3
Medium Priority
?
354 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 1600 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

612 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