May 23, 2003 at 4:35 am
Hi,
I have 2 tables
TblStoreInventory
(
productId REFERENCES tblProduct.productId
)
tblProduct
(
productId
)
tblStoreInventory contains (among other things) the product Ids’s of products that are contained within a store.
I would like to obtain a table that contains the product Id’s of all the products that are:
1)IN tblProduct , but
2)NOT in tblStoreInventory
I have created a UDF that returns a table that lists all of the product Id’s that are in tblStoreInventory
CREATE FUNCTION GetStoreInventoryProductIds (@clientId INT, @storeId INT)
RETURNS @tblStoreInventoryIds TABLE
(
prodId INT
)
AS
BEGIN
INSERT @tblStoreInventoryIds
SELECT prodId
FROM tblStoreInventory
WHERE clientId = @clientId
AND storeId = @storeId
RETURN
END
So, I think that I am looking for the opposite of an INNER JOIN, but I have not been able to get the answer.
Can anyone point me in the right direction?
Many thanks,
yogi
May 23, 2003 at 4:46 am
you can use EXISTS
SELECT ProductId
FROM tblProduct
WHERE EXISTS (
SELECT 1
FROM TblStoreInventory
WHERE TblStoreInventory.ProductId = tblProduct.ProductId
)
--RETURN PRODUCTS IN INVENTORY
SELECT ProductId
FROM tblProduct
WHERE NOT EXISTS (
SELECT 1
FROM TblStoreInventory
WHERE TblStoreInventory.ProductId = tblProduct.ProductId
)
--RETURN PRODUCTS NOT IN INVENTORY
May 23, 2003 at 6:22 am
Another way:
--In Inventory:
SELECT ProductId
FROM tblProduct
INNER JOIN TblStoreInventory
ON TblStoreInventory.ProductId = tblProduct.ProductId
--Not in Inventory:
SELECT ProductId
FROM tblProduct
LEFT JOIN TblStoreInventory
ON TblStoreInventory.ProductId = tblProduct.ProductId
WHERE TblStoreInventory.ProductID IS NULL
You should run both in Query Analyzer and turn stats on / exec plan on to see which might be more efficient...
HTH,
Jay
May 23, 2003 at 7:37 am
You can try :
SELECT ProductId
FROM tblProduct
WHERE ProductId NOt in (
SELECT ProductId FROM TblStoreInventory
)
tung
Luani
Luani
May 23, 2003 at 8:27 am
Howdy,
Thanks folks, they all work great.
One thing though..
I was advised to
“ use Query Analyzer and turn stats on / exec plan on to see which might be more efficient”
I was wondering…what ultimately is the measure of the greatest efficiency, is it the “ Cumulative client processing time” ?
Many thanks..
yogi
May 23, 2003 at 8:32 am
quote:
I was wondering…what ultimately is the measure of the greatest efficiency, is it the “ Cumulative client processing time” ?
Efficiency can be measured by execution time and IO reads (both logical and physical). I generally tend to prefer query plans which produce the least IOs, as these plans tend to be more scalable.
May 23, 2003 at 9:41 am
Sweet,
Thanks. yogi
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply