On Wed, 2005-12-28 at 09:56 +1100, Nigel Weeks wrote:

> > Yeah, I need to also include anoter identifier there like
> > login_name='joe' had played with that as well, same results.
> > Here is a corrected version.
> >
> > SELECT COUNT(STATUS) FROM CONTACT_LOGIN_LOG
> > WHERE LOGIN_NAME='joe' AND STATUS='Failed' AND LOGGED IN (
> > SELECT FIRST 3 LOGGED FROM CONTACT_LOGIN_LOG
> > WHERE LOGIN_NAME='joe' ORDER BY LOGGED DESC);
> >
> > Being that LOGGED is a primary key, I thought having the
> > login_name was
> > redundant or unneccessary?
>
> Being a primary key in completely irrelevant. You've got three values in an
> 'IN' condition. Nothing more than that.

LOGGED in the outer query would equal only those three right? Since they
are primary keys would make the outer query have the equivalent of 3 x
LOGGED= conditions? For example if LOGGED where an int, and had values
of 1,2,3. Would the outer query not be restricted to returning only rows
with the primary keys of 1,2,3?

Now at the moment there are 6 failed entries for joe. When I run the
above query I get 6. If I just return the rows with * instead of
COUNT(). I get all records, not just ones in the inner query.

> Fwar. There's a lot of tests for that! There might be a simpler way...
> Are you using this for a system that does the following:
>
> "Welcome back, mr. <surname>. There have been three(3) unsuccessful login
> attempts since you last logged in."

No. More to lock and flag the account, then to notify someone or take
further action. There will be further logic, this is just to get a
result for further testing and other conditionals.

Which is partly why a query would be ideal, or in place a stored proc to
be called.

--
Sincerely,
William L. Thomson Jr.
Obsidian-Studios, Inc.
http://www.obsidian-studios.com