Tough query question - 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 2005 Forums

Transact-SQL (2005)

Tough query question

Author

Topic

cmspot
Starting Member

44 Posts

Posted - 2007-12-05 : 12:57:51

Hello everybody,I need your help for one more time.
Here is my problem...
I have a table which keeps products.The productid is a number.It starts From 1000 until 25000.I do not want this field identity because i want to insert the code manually.
As you see i Have about 24000 rows but what happens when i delete one product?
My problem is that I want to find the productid that is not used or it is deleted?
for example (1000,1001,1003,1004) "1002 Was deleted"
How can i find 1002 So i can use it again?
I ve made a trigger that inserts the productid into another table when a product is deleted and when i insert a new product i search if exists this code in this table and if exists it deletes it.
But i do not like this Way.It sounds a little as a pattent to me and i do not like tricks in programming.
Is there any other way to find the gaps?

"what if i used a cursor with two variables and if the difference between the two variables was bigger than one then the gap was found?"
But i do not like using cursors too!They are too slow...
thank you for your help.......

I sell my mother in law.Is anybody interested?

X002548
Not Just a Number

15586 Posts

Posted - 2007-12-05 : 13:08:26

You sure you want to do that? Wouldn't that get confusing if product a (deleted) got confused with product b

And when do you need to find the gap?

When you want to add a new product?

Why would you need to find all gaps at once/

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-12-05 : 13:54:49

As Brett's questions are eluding to, unless you have a very compelling reason to reuse a number you should just use a new one. That being said, if you create a Numbers table you could do a simple LEFT OUTER JOIN to get the missing values, for example:

SELECT
	Numbers.Number
FROM
	Numbers
LEFT OUTER JOIN
	MyTable
	ON Number.Number = MyTable.ProductID
	AND Numbers.Number BETWEEN (SELECT MIN(ProductID) FROM MyTable) AND (SELECT MAX(ProductID) FROM MyTable)
WHERE
	AND MyTable.ProductID IS NULL

cmspot
Starting Member

44 Posts

Posted - 2007-12-05 : 14:05:45

quote:
[i]
And when do you need to find the gap?

When you want to add a new product?

Why would you need to find all gaps at once/

Sorry I do not what to find all gaps at once.I want the first available.
I need to find the gap as an available new code only if the product has no moves (but i can define it on another way)

I sell my mother in law.Is anybody interested?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-12-05 : 14:09:54

Try this:

DECLARE @Yak TABLE(Num INT)

INSERT @Yak
SELECT 1000
UNION ALL SELECT 1001
UNION ALL SELECT 1003
UNION ALL SELECT 1004
UNION ALL SELECT 1007


SELECT
	MIN(Yak.Num + 1) AS Num
FROM
	@Yak AS Yak
WHERE    
	NOT EXISTS 
	(
		SELECT 1 
		FROM @Yak AS Temp 
		WHERE Yak.Num + 1 = Temp.Num
	) 
	AND Yak.Num < (SELECT MAX(Num) FROM @Yak) 

-- Or this
SELECT
	MIN(Yak.Num + 1)
FROM
	@Yak AS Yak
LEFT OUTER JOIN
	@Yak AS Yak1
	ON Yak.Num + 1  = Yak1.Num 
WHERE
	Yak1.Num IS NULL

cmspot
Starting Member

44 Posts

Posted - 2007-12-05 : 14:43:39

My friend Lamprey it works and thank you very much for that but can you please explain me what do you do inside the parenthesis..I mean
in the not exists Clause..
What it is this Select 1 from ......

I sell my mother in law.Is anybody interested?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-12-05 : 15:56:23

Basically, NOT EXISTS is checking for the nonexistance of something. So, in this case if Num + 1 = Num then the SELECT will return 1 and therefore it does EXIST and does not meet the criteria of NOT EXISTS. So, you could pretty much put anything in place of the 1 (like * or Num or whatever). Hopefully, that makes sense. Feel free to look up NOT EXISTS in Books Online and you can read mroe about it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources