How select for not used codes in this sample

Question

I have a int column in my table in a SQL database. I keep some of codes in this table. For sample range of my codes is : (1, 9).
I need to not used code in this range.

Example:
Used Codes is :

Select code from MyTable  -- result is 2,6,7,8,9

Not used codes is : 1,3,4,5

Now , how can I select this codes? Expected result is : 1,3,4,5

@danish List of used codes is in a table in database. My real range is constant : (1000,25000) — Ehsan, Aug 14 '12 at 08:46

score 4 · Accepted Answer · edited May 23 '17 at 12:20

4

Generate a list of numbers for your range (here I use VALUES clause), then semi-join to the "used" list

SELECT
    *
FROM
   (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9)) X (Num)
WHERE
   NOT EXISTS (SELECT * FROM MyTable M WHERE M.Code = X.Num)

Edit:

You can replace the VALUES clause with any number table generation code. Examples:

edited May 23 '17 at 12:20

Community

1
1

answered Aug 14 '12 at 08:33

gbn

422,506
82
585
676

Range of (1,9) is for example. My real range is (1000,25000).Now How can I do ? – Ehsan Aug 14 '12 at 08:43

score 1 · Answer 2 · answered Aug 14 '12 at 09:04

IMHO you should maintain the list of valid codes in a look up table in the database and have the used codes as a foreign key field in the other table (MyTable in your post).

Assuming you have created a table to save codes with name LookUpTable that has a code id and code. In the other table, you have code id as foriegn key. Then, you can use following query:

SELECT  LOOKUPTABLE.Code 
FROM    LOOKUPTABLE LEFT JOIN FOREIGNKEYTABLE ON LOOKUPTABLE.ID = FOREIGNKEYTABLE.CodeID 
WHERE FOREIGNKEYTABLE.USERID IS NULL

How select for not used codes in this sample

2 Answers2

Linked

Related