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.
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'
)