CODEWITHSUNDEEP
sql-servert-sql
Edward Rhoades
Edward Rhoades

Reputation: 31

WHERE NOT EXISTS Query Not Working

I'm trying to find out if there are any orders in the inventory_detail table (the order number is actually on inventory_header) where either the load_store OR the unload_store value does not exist at all in the store_Code field in extrainfo_stores table.

The query below returns some orders where, when I look in the extrainfo_stores table for a order returned, I see the exact value that is in load_store or unload_store in that table, so it's obviously not working...

 SELECT  bh.ord_number, bi.*
 FROM    inventory_header bh
 inner join inventory_detail bi on inventory_id = bh.inventory_id
 WHERE   NOT EXISTS
    (
    SELECT  null 
    FROM    extrainfo_stores d
    WHERE   d.ord_number = bh.ord_number
      and   bi.load_store <> d.store_code
      and   bi.unload_store <> d.store_code
    )

Upvotes: 0

Views: 92

Answers (2)

paparazzo
paparazzo

Reputation: 45106

Is this what you are looking for?

SELECT bh.ord_number, bi.*
 FROM  inventory_header bh
 join  inventory_detail bi 
   on  inventory_id = bh.inventory_id
 join  extrainfo_stores d
   on  d.ord_number = bh.ord_number
Where bi.load_store   <> d.store_code
   or bi.unload_store <> d.store_code

Upvotes: 0

user2422457
user2422457

Reputation:

It should be something like: 

SELECT bh.ord_number, bi.*
  FROM inventory_header bh
 inner join inventory_detail bi on inventory_id = bh.inventory_id
 WHERE NOT EXISTS (SELECT null 
                     FROM extrainfo_stores d
                    WHERE d.ord_number = bh.ord_number
                      and (bi.load_store = d.store_code or bi.unload_store = d.store_code))

Upvotes: 1

Related Questions