Not exists query is not returning expected info

Question

I have a query, and for some reason I'm not getting the orderid 10 info as expected. The results give no lines. The query without the not exists part gives the below info.

The table looks like this:

orderid     id                number         fulfilledproduct                                                                                                                                                                                                                                                                                               
2           BundleSpec        1              ID                                                                                                                                                                                                                                                                                                                            
2           TemplateSpec      1              IDSheet                                                                                                                                                                                                                                                                                                 
2           TemplateSpec      1              IDCertificate                                                                                                                                                                                                                                                                                                               
10          BundleSpec        1              ID                                                                                                                                                                                                                                                                                                                            
10          TemplateSpec      1              IDSheet     

                                                                                                                                                                                                                                                                                                       

For some reason my query returns 0 lines when I try to get the orderid without the IDCertificate.

It's probably something I need to fix for the not exists part since I don't do that very often. Any ideas?

select (orderid),(id),(number),(fulfilledproduct) 
from [Product] 
where 
  id in ('BundleSpec','TemplateSpec') 
  and not exists 
  (
    select (orderid) 
    from [Product] 
    where 
      id in ('BundleSpec','TemplateSpec') 
      and fulfilledproduct = 'IDCertificate'
  )

I thought I was following an example correctly. Should I use except instead like except does?

Expected Output:

orderid         id                number         fulfilledproduct                                                                                                                                                                                                                                                                                            
                                                                                                                                                                                                                                                                                                            
    10          BundleSpec        1              ID                                                                                                                                                                                                                                                                                                                            
    10          TemplateSpec      1              IDSheet  

Note that this is using a dlx sql command on a device at the command line, and my example had the () and [] for the table info in the query so I'm using that too. It should give output the same way microsoft sql works in sql server as I am told. I have formatted the query for easy viewing and removed the command line syntax.

Answer 1

This is because your not-exists clause has nothing tying it to the main query. Your not-exists query always returns a row, so therefore the main query returns nothing.

You need to tell it something like this:

select (orderid),(id),(number),(fulfilledproduct) 
from [Product] p
where 
  id in ('BundleSpec','TemplateSpec') 
  and not exists 
  (
    select 1
    from [Product] p2
    where 
      p2.orderid = p.orderid 
      and p2.fulfilledproduct = 'IDCertificate'
  )