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

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.