question about results where i dont have a match

lets say I have a table called "billing"  that has thousands of accounts and then inside the billing table there  is a column called optionskey.  the column optionskey can be anyone of 100 numbers.  4 digits.  this column corresponds to an optionskey table that describes the option.

ie... optionskey will translate to garbage... or water.

so I have a query with 200 possible options keys.

so I would run a query that says... select * from billing where optionskey in ('1001', 1002,'1115',etc....)
in the parenthesis, I want to supply it 200 optionkeys.  I want to know which optionskeys are not in the table....


example table

accountnumber optionskey
1111                        1004
2222                        1005

select * from billing where optionskey in ('1004', '1005', '1006')
I would like to know that there was no hit for an optionskey of '1006'

any ideas
jamesmetcalf74Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
Select optionskey2 from billingTemp
Except     -- if sql server 2008 and above
select optionskey from billing


And enter in new table called billingTemp   '1001', 1002,'1115'.... in column optionskey2

Note: Also, you can add a Yes/No column to exclude certain options keys.

 Table billingTemp  (You most likely have a table like this to use. If so, then you don't have to add this new table)  
optionskey2        optionskeyName       ExcludeYN
----------------          -------------------------       ----------
 1001                       garbage                          1
1002                        water                              0
1115                        ....                                    0

With ExcludeYN column, try:
Select optionskey2 from billingTemp Where ExcludeYN = 0
Except     -- if sql server 2008 and above
select optionskey from billing

With this solution, 1001/garbage will not be included in the output.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chaauCommented:
I would use a LEFT JOIN:
SELECT o.optionskey, b.accountnumber,
CASE WHEN b.optionskey IS NULL THEN 'option Not present' ELSE 'option IS present' END result
from optionskey o LEFT JOIN billing b
ON o.optionskey = b.optionskey
where o.optionskey in ('1004', '1005', '1006')

Open in new window

jamesmetcalf74Author Commented:
thansk guys!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.