Check [data's existence] from another table
-
hi guys, i have two tables PurchaseOrders and ArrivedItems
i want to get all the PurchaseOrderNos that doesn't exist in the ArrivedItems
consider this analogy, the item is purchased first, then after settling the payment, the next scenario is the arrival of item
anyway i tried to create a query for it,
SELECT PurchaseOrderNo
FROM PurchaseOrders
WHERE (NOT EXISTS
(SELECT *
FROM ArrivedItems))
but i don't know wha'ts wrong, it always return NULL,
-
What about a LEFT JOIN...
SELECT
*
FROM PurchaseOrders po
LEFT JOIN ArrivedItems ai ON pon.PurchaseOrderNo = ai.PurchaseOrderNo
WHERE ai.PurchaseOrderNo IS NULL
-
Flo's suggestion is a good solution for this, and you would do well to study it and understand how it works -- the "Left outer join" is a powerful tool you'll want to have in your belt.
Now, let's go back to the original code and why it always returns NULL. It has nothing in it to define the relationship between the two tables. Just because a column has been specified as a "foreign key" to another table does not mean it's automatically used to connect the data; it's just used to maintain "referential integrity", preventing the deletion of a parent (purchase order) by itself and leaving orphans in another table (arrived items) that would belong to the parent.
So, what happened in your query is that the SQL engine looked at your "where" clause to see which purchase orders you wanted back, and determined you only want data when there is no data in the ArrivedItems table at all. It looks for a "not exists" condition from a subquery that selects everything ("*") from arrivedItems.
You could still use this approach by simply supplying the link between the two tables.
SELECT PurchaseOrderNoFROM PurchaseOrders
WHERE NOT EXISTS
(SELECT *
FROM ArrivedItems
where ArrivedItems.PurchaseOrderNo
= PurchaseOrders.PurchaseOrderNo )Now the subquery will return a true/false for finding matching rows in the items table. This is called a "correlated" subquery because its results depend on (are correlated to) the values in the main query using it.
Since either method works (and I don't recall ever learning that there was a significant performance difference between them), I usually would go with the left outer join that Flo showed you. BUT.... if you're going to be doing much work with SQL, you'll want to understand both.
edit: added note about term "correlated".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply