missing records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

missing records

Author

Topic

doran_doran
Posting Yak Master

179 Posts

Posted - 2008-07-16 : 12:30:02

How do i determine records from a table "tblCustomers" when I know for fact the id column has the next increment number. for example,

id customer
1 abc company
2 xyz company
4 rrr company

in this scenario i can see i am missing the id 3. I downloaded a table and it has missing records like this and I need to be able to determine those.

Thanks

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-07-16 : 12:56:36

Maybe someone else has a better idea, but it works:

-- your downloaded table
create table test_it(
id int,
customer varchar(10))

-- your example-data
insert test_it (id,customer)
select 1,'abc'
union select 2,'xyz'
union select 4,'rrr'

-- show it
select * from test_it

-- table for the needed values
create table without_gap(
good_id int identity(1,1),
something int)

-- insert needed values
insert without_gap (something)
select id from test_it

-- find missing ids
select good_id as id_is_missing
from without_gap wg
left join test_it ti on wg.good_id = ti.id
where ti.id is null

Greetings
Webfred

There are 10 types of people in the world: Those who understand binary, and those who don't...

doran_doran
Posting Yak Master

179 Posts

Posted - 2008-07-16 : 13:51:23

I will try this. Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-16 : 14:07:10

or use not in

select * from without_gap where id not in (select id from with_gap)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2008-07-16 : 14:41:17

Hi visakh16,
I've detected that, at latest in SQL Server 2005, the join-method is much faster than the not-in-method.
Probably it's depending on how much rows are the tables.

In our company, we speak about the "bad not in".

Greetings
Webfred

There are 10 types of people in the world: Those who understand binary, and those who don't...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-07-16 : 15:06:02

If you have a Number table you could just join to that and determine which are missing:

SELECT Num.Num
FROM Num
LEFT OUTER JOIN Customer
ON Num.Num = Customer.ID
WHERE Num.Num <= (SELECT MAX(ID) FROM Customer)

doran_doran
Posting Yak Master

179 Posts

Posted - 2008-08-06 : 13:39:00

Lamprey,

I modified your statement a little bit and now it seems to be working.

SELECT t.id
FROM test_id AS t LEFT JOIN slip ON t.id = slip.ID
WHERE (((t.id)<=(SELECT MAX(ID) FROM slip)) AND ((slip.id) Is Null));

Thanks
Moe

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources