Select records in a table, where the data in another table determines which records are shown-CodePudding

I have two tables, the first one is Purchase_Orders (PO), the second is Purchase_Order_Items (POI).

I need to select all the Purchase Orders from PO that still have items to be delivered from POI.

So don't show a Purchase Order if all of the Delivered column from POI equals 1, otherwise it should show the Purchase Order in the record set.

The relationship between the tables are

PO.Order_No = POI.Order_No

CodePudding user response：

You could use exists logic here:

SELECT po.*
FROM Purchase_Orders po
WHERE EXISTS (
    SELECT 1
    FROM Purchase_Order_Items poi
    WHERE poi.Order_No = po.Order_No AND
          poi.Delivered <> 1
);

The exists subquery might benefit from the following index:

CREATE INDEX idx ON Purchase_Order_Items (Order_No, Delivered);