Why no worky?

October 21, 2006 5:42 PM

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:

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!

posted by SoulOnIce to Technology (4 answers total)

Maybe try fully-qualifying the field names with the table names? E.g.:

SELECT ponumber FROM vwPO WHERE vwPO.poNumber NOT IN (SELECT ponumber FROM purchaseorders)

posted by Doofus Magoo at 6:46 PM on October 21, 2006

My guess is you have a NULL ponumber in purchaseOrders.

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

Don't you have it backwards? From your description, the extra records are in the purchaseorders table, not the view, so you want

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