SQL Statement - JOINing tables

[jhappy3]

I have a MS SQL Server 7 database containing two tables, ORDER_INFO and SHIPNOTIFY. The ORDER_INFO table is automatically populated with data from online ecommerce transactions. A unique ORDERID number is given each record as well as a unique InvoiceNumber in the ORDER_INFO table. The SHIPNOTIFY table is populated with shipping information after an order has been received and the shipment sent. The SHIPNOTIFY table has fields called SOrderID and SInvoiceNumber (as well as other shipping data fields). Lets say a customer completes an online transaction. The shopping cart adds a record to the ORDER_INFO table with an ORDERID = 1 and an INVOICENUMBER = 1. Another order is received and the ORDER_INFO table is populated with another record which contains ORDERID = 2 and INVOICENUMBER = 2. As the first shipment goes out, the store admin. adds a record to the SHIPNOTIFY table which contains SOrderID = 1 and SInvoiceNumber = 1 and data in a field called SHIPSTATUS which contains either a "C" (completely shipped order) or a "P" (partially shipped order). If all goes well the ORDER_INFO table will always have just a few more records in it than the SHIPNOTIFY table.

I need to create a SQL statement that compares data in both tables and returns ORDERID 's that have not been shipped and those SOrderID 's that have only been paritally shipped. I want to compare the ORDERID field in the ORDER_INFO table with the SOrderID field in the SHIPNOTIFY table and return ONLY those ORDERID 's that cannot be found in the SHIPNOTIFY table AND those orders that have only been partially shipped.

ORDER_INFO Table

ORDERID INVOICENUMBER
|-----------|--------------|
|....1........|......1..........|
|-----------|--------------|
|....2........|......2..........|
|-----------|--------------|
|....3........|......3..........|
|-----------|--------------|
|....4........|......4..........|
|-----------|--------------|

SHIPNOTIFY Table

SOrderID SInvoiceNumber ShipStatus
|-----------|--------------|-------------|
|....1........|......1..........|.....C.........|
|-----------|--------------|-------------|
|....2........|......2..........|.....P.........|
|-----------|--------------|-------------|

(Please ignore the periods (.) in the above representations of database tables. They were necessary to make the 'data' in the records stay near the middle of each row and column.)

If, at a certain point in time, the two tables contained the above data, I want to query the database as follows:

To receive all ORDERID 's from the SHIP_INFO table that WERE NOT present in the SOrderID column AND ORDERID 's that WERE present in the SOrderID column BUT ONLY if the SHIPNOTIFY record contained a ShipStatus = "P". "P" would mean the shipment was only "Partially" shipped.

The query results I want would contain a recordset with a field containing three records, OrderID = 2, OrderID = 3, and OrderID = 4.

I think I need a SQL Statement containing a LEFT OUTER JOIN .. but I'm not sure.

Please help me design a SQL Statement that discovered the unfulfilled and partially fulfilled orders.

 

[MacLeod72]

You could try:

SELECT
    ORDERID
FROM
    ORDER_INFO
WHERE
    ORDERID NOT IN
    (
        SELECT
            SOrderID
        FROM
            SHIPNOTIFY
        WHERE
            ShipStatus <> 'P'
            OR ShipStatus IS NULL
    )

--Rob