Here's one way...

 

select Counter from

(SELECT DISTINCT storeNumber FROM tblStore) storeNumbers

RIGHT OUTER JOIN

(

    select n1*10 + n2 + 1 [Counter]

    from 

    (select 0 [n1] union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) list1

    cross join

    (select 0 [n2] union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) list2

) x

ON StoreNumber = Counter

WHERE StoreNumber is NULL

Cheers,

- Mark

Edited by - mccork on 07/31/2003 5:51:36 PM