Cross table SQL Query to exclude from results records based on keyword

Question

Here's a head-scratcher (for me)...

I have two tables A and B. A contains product descriptions, B contains product codes, quantity, and accounts. The wrinkle here is that B has a parent, child relationships on product codes.

What I would like to do is put together a result set where I see Account, Product, Description and Qty, where one of the product Qty's = 0, EXCEPT if there's a keyword in the Description in the product (whose Qty I don't care about).

So, here's my query right now:

SELECT Bp.Account, Bp.Product, A.Description, Bp.Qty FROM B 
AS Bp JOIN A as Prod  ON (Bp.Product = Prod.Product)  
WHERE EXISTS (Select * from B AS Bc 
              WHERE Bp.Account = Bc.Account
              AND Qty = 0)

And this returns a very nice result set of:

Account  Product Description Qty
ABC      XYZ     DESC-BLAH   1
ABC      XYY     DESC-DEE    0
ABC      ZXY     DESC-BILP   1
BDE      XAZ     DESC-OOPS   1
BDE      XYY     DESC-DEE    0
BDE      ZXY     DESC-BLIP   1

So lets say I want to exclude DESC-OOPS, and all products relating to it from my result set. I would like to see:

Account  Product Description Qty
ABC      XYZ     DESC-BLAH   1
ABC      XYY     DESC-DEE    0
ABC      ZXY     DESC-BILP   1

Is this possible to do in a single query?

Many thanks in advance!

Answer 1

Perhaps you could just add to your WHERE criteria:

SELECT Bp.Account, Bp.Product, A.Description, Bp.Qty FROM B 
AS Bp JOIN A as Prod  ON (Bp.Product = Prod.Product)  
WHERE EXISTS (Select * from B AS Bc 
              WHERE Bp.Account = Bc.Account
              AND Qty = 0)
  AND Bp.ACCOUNT NOT IN (SELECT ACCOUNT FROM Table WHERE Description = 'DESC-OOPS'

I'm not sure why that doesn't work for you, but perhaps:

SELECT *
FROM (SELECT Bp.Account, Bp.Product, A.Description, Bp.Qty 
      FROM B AS Bp 
      JOIN A as Prod  
         ON (Bp.Product = Prod.Product)  
      WHERE EXISTS (Select * from B AS Bc 
                  WHERE Bp.Account = Bc.Account
                  AND Qty = 0)
      )sub
WHERE ACCOUNT NOT IN (SELECT ACCOUNT FROM Table WHERE Description = 'DESC-OOPS'