Why no worky?
October 21, 2006 5:42 PM Subscribe
SQLServerFilter: Quick question about views and using "NOT IN".
Okay, this should be easy, but for some reason my query isn't returning the desired results. I have a view, vwPO, with one field called poNumber. I have a table called purchaseOrders with many fields, one of which is also called poNumber.
vwPO is not generated from the purchaseOrders table. vwPO has 19,925 distinct records, purchaseOrders has 20,382 distinct records.
I need to return all poNumbers in vwPO that are not in the purchaseOrders table.
If I run the following query, I get nothing, an empty recordset:
If I use IN instead of NOT IN, the query returns 19661 records. Why is my first query not returning the 264 poNumbers?
I know for a fact that there are poNumbers in the view that are not in the table, I just don't understand why they aren't being returned.
Admittedly my brain is fried right now and I feel like I'm missing something easy....but at the moment, I don't get it.
Help, please! Thanks!
Okay, this should be easy, but for some reason my query isn't returning the desired results. I have a view, vwPO, with one field called poNumber. I have a table called purchaseOrders with many fields, one of which is also called poNumber.
vwPO is not generated from the purchaseOrders table. vwPO has 19,925 distinct records, purchaseOrders has 20,382 distinct records.
I need to return all poNumbers in vwPO that are not in the purchaseOrders table.
If I run the following query, I get nothing, an empty recordset:
SELECT ponumber FROM vwPO WHERE poNumber NOT IN (SELECT ponumber FROM purchaseorders)
If I use IN instead of NOT IN, the query returns 19661 records. Why is my first query not returning the 264 poNumbers?
I know for a fact that there are poNumbers in the view that are not in the table, I just don't understand why they aren't being returned.
Admittedly my brain is fried right now and I feel like I'm missing something easy....but at the moment, I don't get it.
Help, please! Thanks!
My guess is you have a NULL ponumber in purchaseOrders.
Try this:
posted by blue mustard at 8:45 PM on October 21, 2006
Try this:
SELECT ponumber FROM vwPO WHERE poNumber NOT IN
(SELECT ponumber FROM purchaseorders WHERE ponumber IS NOT NULL)
posted by blue mustard at 8:45 PM on October 21, 2006
I can't answer your question, but I would do it like this:
SELECT ponumber FROM vwPO
LEFT OUTER JOIN purchaseorders
ON vwPO.poNumber = purchasorders.ponumber
WHERE purchaseorders.ponumber IS NULL
posted by smackfu at 8:59 PM on October 21, 2006
SELECT ponumber FROM vwPO
LEFT OUTER JOIN purchaseorders
ON vwPO.poNumber = purchasorders.ponumber
WHERE purchaseorders.ponumber IS NULL
posted by smackfu at 8:59 PM on October 21, 2006
Don't you have it backwards? From your description, the extra records are in the purchaseorders table, not the view, so you want
Also, if the view is based on the table, why are there records missing? What does the view query look like? There may be a criterion that excludes some records from the view.
posted by jimfl at 9:12 AM on October 22, 2006
select ponumber from purchaseorders where ponumber not in (select ponumber from vwPO)Or have I missed something?
Also, if the view is based on the table, why are there records missing? What does the view query look like? There may be a criterion that excludes some records from the view.
posted by jimfl at 9:12 AM on October 22, 2006
« Older Where can I find this obscure recording? | What is a good metaphor and a good analogy for... Newer »
This thread is closed to new comments.
posted by Doofus Magoo at 6:46 PM on October 21, 2006