question about results where i dont have a match

jamesmetcalf74
jamesmetcalf74 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Database and Application Developer
Commented:
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.
chaau
Top Expert 2013
Commented:
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

jamesmetcalf74

Author

Commented:
thansk guys!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial