By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,806 Members | 1,841 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,806 IT Pros & Developers. It's quick & easy.

Problem with nestd query

100+
P: 121
Hi All,
scenerio is:
whenever user tries to change his password, his new password is sent to database and now i have a query which retrieves all the last changed password by this user with query (in my case only 5 matters)

Expand|Select|Wrap|Line Numbers
  1. select top 5 columnname from tablename where id=@id.
now what i am unable to do is to check his supplied password to be one of this 5 entries. means if he gives new password as the one which is already in his last 5 entries, he should be prompted to give some another password which should not be in last 5 entries

hope i am clear.

what i have tried id:

Expand|Select|Wrap|Line Numbers
  1. select count(*) from PwdHistory where @Suppliedpwd  
  2.  in (select top 5 columnname from PwdHistory where (UserId=@userid))
where @Suppliedpwd is new password supplied, @userid= userid of person we are checking last 5 passwords, columnname is column which contains passwords
any help will be appreciated
Apr 14 '09 #1
Share this Question
Share on Google+
4 Replies


mwasif
Expert 100+
P: 801
Moved to SQL Server forum.
Apr 14 '09 #2

ck9663
Expert 2.5K+
P: 2,878
Let me see if I got you. You want to get the last 5 password changes of the user. If the new password that he wants today happens to be earlier than the top 5, you will allow it. If am reading you wrong, don't proceed coz this entire suggestion is wrong. Otherwise, read on...

Try this query.

Expand|Select|Wrap|Line Numbers
  1. if exists (select 1 from (select top 5 * from PwdHistory where UserId=@userid)  where @Suppliedpwd = columnname)
  2.  
This query will not really run as it is, you have to use function or SP.


--- CK
Apr 15 '09 #3

100+
P: 121
thankks Ck for ur reply, yes u got me right and i just mangled ur provided query and the following query solves my purpose

Expand|Select|Wrap|Line Numbers
  1. select * from tablename where pwdcompare(@NewPassword,passwordcolumn,0)=1 and passwordcolumn 
  2.      in (select top 10 passwordcolumn from tablename where UserId=@UserId order by ID desc) 
Apr 15 '09 #4

P: 13
create table #test (id INT identity(1,1), Password Varchar(10))
insert into #test (Password) values ('a')
insert into #test (Password) values ('b')
insert into #test (Password) values ('c')
insert into #test (Password) values ('d')
insert into #test (Password) values ('e')
insert into #test (Password) values ('f')
insert into #test (Password) values ('g')
insert into #test (Password) values ('h')
insert into #test (Password) values ('i')

Declare @NewPassword varchar(10)
set @NewPassword = 'a'

-- If the Result is 1 then the Password exists, user need to create new password.
-- If the Result is 0 then the Password not exists, so allow user need to save the new password.

Select count(1) as Result From
(select top 5 password from #test order by id Desc) A
where a.password = @NewPassword
-- Output
-- Result
-- 0

Declare @NewPassword varchar(10)
set @NewPassword = 'e'

Select count(1) as Result From
(select top 5 password from #test order by id Desc) A
where a.password = @NewPassword
-- Output
-- Result
-- 1

Use the filter for User and try it.

Reg,
JK
Apr 16 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.