how to find records that are not in a table? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

how to find records that are not in a table?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-08-01 : 15:08:02

gibb writes "Hi,

I'd like to know how to write a query that finds records that are not in a table.

Say, i have a list of Ids, 1, 2, 3, and 4. Ids 1, 2, and 4 exist in table_A but not Id 3. What kind of query would return me Id 3?

Thanks in advance!
gibb"

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-08-01 : 15:35:16

What do you want returned if you have multiple gaps?

Say your table looks like this:

declare @yourTable table (yourID int)
insert into @yourTable
	select 1 union select 2 union select 4 union select 8
select * from @yourTable

Nathan Skerl

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-08-01 : 19:02:11

The query will give you a list of missing IDs.

The code for the table function dbo.F_TABLE_NUMBER_RANGE can be found in this topic:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685


declare @min_id int
declare @max_id int

select
	@min_id = min(ID)
	@max_id = max(ID)
from
	table_A

select
	MISSING_ID = a.NUMBER
from
	dbo.F_TABLE_NUMBER_RANGE(@min_id,@max_id) a
	left join
	table_A b
	on a.NUMBER = b.ID
where
	b.ID is null

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-08-02 : 01:07:43

Refer this also
http://mindsdoor.net/SQLTsql/FindGapsInSequence.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources