SQL help - 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)

SQL help

Author

Topic

yolip
Starting Member

4 Posts

Posted - 2006-05-03 : 21:38:49

Can anyone tell me how to make this work?

SELECT MAX(AssetID) + 1 AS newkey
FROM tbAsset
WHERE (newkey NOT IN
(SELECT KeyInUse
FROM tbKeyInUse))

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-03 : 21:44:53

[code]
SELECT newkey
FROM
(
SELECT MAX(AssetID) + 1 AS newkey
FROM tbAsset
) n
WHERE NOT EXISTS (SELECT KeyInUse FROM tbKeyInUse x WHERE x.KeyInUse = n.newkey)
[/code]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-04 : 01:48:52

quote:
Originally posted by yolip

Can anyone tell me how to make this work?

SELECT MAX(AssetID) + 1 AS newkey
FROM tbAsset
WHERE (newkey NOT IN
(SELECT KeyInUse
FROM tbKeyInUse))

thanks

Did you get error or wrong result?

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-04 : 01:51:38

quote:
WHERE (newkey NOT IN
(SELECT KeyInUse
FROM tbKeyInUse))

It would gives error as newKey is a column alias

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-04 : 02:05:49

Thanks. Yes it is.

Other way would be

SELECT MAX(AssetID) + 1 AS newkey
FROM tbAsset
having (MAX(AssetID) + 1 NOT IN
(SELECT KeyInUse
FROM tbKeyInUse))

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-04 : 05:50:50

Hi all,

Yolip - This seems like an odd request to me (since no rows will sometimes be returned), so I wonder if you meant to ask a different question.

It's just a guess, but maybe you want get the 'next' key (for tbAsset) which isn't already in use (i.e. in tbKeyInUse).

If so, then this might be of help...

--data
declare @tbKeyInUse table (KeyInUse int)
insert @tbKeyInUse
          select 1
union all select 4
union all select 5
union all select 7

declare @tbAsset table (AssetID int)
insert @tbAsset
          select 1
union all select 2
union all select 3

--calculation
declare @numbers table (i int identity(1, 1), j bit)
insert @numbers select top 1000 null from master.dbo.syscolumns a, master.dbo.syscolumns b

select min(i) from @numbers
where i > (select max(AssetID) from @tbAsset) and i not in (select KeyInUse from @tbKeyInUse)

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources