How to get missing Nos. fro - 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)

How to get missing Nos. fro

Author

Topic

M62431
Starting Member

2 Posts

Posted - 2005-06-17 : 23:29:32

If anybody know how to get missing nos. from a table with single query without using any other table

I have below table : hdept
Dept_cd dept_nm
1 Accounts
2 SED
4 HRM
7 Marketing
5 Production
9 Mechanical
10 Purchase

etc.

I did it by using temp table. pls let me know whats the simplest method to do it.

10x

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-18 : 14:10:50

You can left join it to a number table to find the missing number.

Here is an example using the number table function from this topic: F_TABLE_NUMBER_RANGE
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685


select
	MISSING_DEPT_CD = a.NUMBER
from
	dbo.F_TABLE_NUMBER_RANGE(1,1000) a
	left join
	HDEPT b
	on a.NUMBER = b.DEPT_CD
where
	b.DEPT_Cd is null
order by
	a.NUMBER

CODO ERGO SUM

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-06-20 : 06:01:07

the requirement "without using any other table"....
conflicts with the solution
"left join it to a number table"

But unless, it is acceptable to have some form of lopoping mechanism (WHILE statement) wrapped around a 'single sql statement' I don't think your requirement can be solved without the number/tally table.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-06-20 : 17:41:35

I have noticed that questions that have restrictions (In a single statement, No using joins, USING a cursor) all seem to be test/homework type crap

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-20 : 19:28:22

I gave him the way I would do it.

You could do this with a correlated sub-query on max where less than the current row, but that's just way too much trouble.

You could also do a left self join on a.DEPT_CD-1 = b.DEPT_CD, but again too much trouble.

You could also generate a derived number table via a cross join in the same query, but I wrote the number table function so I wouldn't have to do stuff like that each time I need it.

If he flunks his homework, I care little. I did my own homework when I was in college. Cull the herd, I say!

quote:
Originally posted by AndrewMurphy

the requirement "without using any other table"....
conflicts with the solution
"left join it to a number table"

But unless, it is acceptable to have some form of lopoping mechanism (WHILE statement) wrapped around a 'single sql statement' I don't think your requirement can be solved without the number/tally table.

CODO ERGO SUM

M62431
Starting Member

2 Posts

Posted - 2005-06-20 : 23:32:26

Thanks Jones.

Its working

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-06-21 : 10:59:30

You could always produce the Numbers table as a subquery. Not real efficient, mind you, but it could be done.

HTH

=================================================================
All restraints upon man's natural liberty, not necessary for the simple maintenance of justice, are of the nature of slavery, and differ from each other only in degree. -Lysander Spooner, lawyer (1808-1887)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources