Hello all.

Below is a select statement that I pieced together.  In a nutshell, it returns a list of consultants with their contact information.  What I'm now trying to do is create a filter by state but I have no idea how.  For example, I want to search all consultants that live in Pennsylvania only.  I was able to create a drop-down list containing all of the states by using the Lists table.  However, I can't seem to get the drop-down value to match the table value.  Any help is appreciated.

Thanks.

SELECT

SUBSTRING(UPPER(MAX(CASE WHEN propertyName = 'LastName' THEN propertyValue END)),1,1) +
SUBSTRING(LOWER(MAX(CASE WHEN propertyName = 'LastName' THEN propertyValue END)),2,25) + ', ' +
SUBSTRING(UPPER(MAX(CASE WHEN propertyName = 'FirstName' THEN propertyValue END)),1,1) +
SUBSTRING(LOWER(MAX(CASE WHEN propertyName = 'FirstName' THEN propertyValue END)),2,25) AS Name,

MAX(CASE WHEN propertyName = 'Street' THEN propertyValue END) + '<br />' +
MAX(CASE WHEN propertyName = 'City' THEN propertyValue END) + ', ' +
MAX(CASE WHEN propertyName = 'Region' THEN propertyValue END) + ' ' +
MAX(CASE WHEN propertyName = 'PostalCode' THEN propertyValue END) AS Address,

'<b>Home:</b> ' +
MAX(CASE WHEN propertyName = 'Telephone' THEN propertyValue END) + '<br />' +
'<b>Cell:</b> ' +
MAX(CASE WHEN propertyName = 'Cell' THEN propertyValue END) AS Phone

FROM

(SELECT DISTINCT PropertyName, PropertyValue, UserID FROM ProfilePropertyDefinition AS ppd
INNER JOIN UserProfile AS up ON up.PropertyDefinitionID = ppd.PropertyDefinitionID) d

INNER JOIN UserRoles ON d.UserID = UserRoles.UserID
INNER JOIN Users ON UserRoles.UserID = Users.UserID
INNER JOIN Roles ON UserRoles.RoleID = Roles.RoleID

WHERE

(Roles.PortalID = 15 AND Roles.RoleName = 'Subscribers')

GROUP BY d.UserID, UserRoles.RoleID, Users.Email, Roles.RoleName

ORDER BY UserRoles.RoleID