I am executing an SQL statement for 1

[lfc77]

I am executing an SQL statement for use in a login, so I need to find certain details on a user based upon their username and password.

The problem I have is that each user has 4 seperate passwords which allow different levels of access. My SQL is currently like this (where 'Name' and 'Pass' are the values entered by the user for their login:

SELECT ID, LoginName
FROM SGS_Users
WHERE LoginName = 'Name'
AND (Password1 = 'Pass' OR Password2 = 'Pass'
OR Password3 = 'Pass' OR Password4 = 'Pass')

How do I change my SQL to find out which of the Password fields matched with the password that the user entered?


Any help would be much appreciated.


Cheers,

Mike

 

[SmileyConspiracy]

I think a case statement similar to this should give you the results you want.

SELECT ID,
LoginName,
CASE WHEN Password1 = 'Pass' THEN 'Password1 Match'
WHEN Password2 = 'Pass' THEN 'Password2 Match'
WHEN Password3 = 'Pass' THEN 'Password3 Match'
WHEN Password4 = 'Pass' THEN 'Password4 Match'
ELSE 'No Match' END MatchStatus
FROM SGS_Users
WHERE LoginName = 'Name'