Hi,

I have two tables...Users and UserProperties

UserProperties has 4 columns:

UserPropertyId INT
UserId INT
PropertyKey VARCHAR(50)
DataValue VARCHAR(MAX)


The following query brings back my user properties:

SELECT UserProperties.*
FROM UserProperties


The following query brings me back ALL UserIds that have a FIRST_NAME set as "Woka":

SELECT UserProperties.UserId
FROM UserProperties
WHERE Userproperties.PropertyKey = 'FIRST_NAME'
AND DataValue = 'Woka'
GROUP BY Userproperties.UserId

The problem arrises when I want to search on multiple fields.

There are 2 ways I can see how to do this:

1) For each field I search on I need to add another INNER JOIN:

SELECT UserProperties.UserId
FROM UserProperties
INNER JOIN UserProperties Props2 ON Props2.UserId = UserProperties.UserId
WHERE UserProperties.PropertyKey = 'FIRST_NAME'
AND UserProperties.DataValue = 'Woka'
AND Props2.PropertyKey = 'LAST_NAME'
AND Props2.DataValue = 'Widget'
GROUP BY UserProperties.UserId

2) Decide what properties are searchable and create a pivot table in a view. I can then do:

SEELCT UserId
FROM vwUserSearch
WHERE vwUserSearch.FIRST_NAME = 'Woka'
AND vwUserSearch.LAST_NAME = 'Widget'

I don't like either.

Anyone got any ideas on this?

cheers,

Woka