Where clause on joined table used for user defined key/value pairs

Answer 1

SELECT UserLogin, FirstName, LastName 
FROM Users 
where UserID in ( 
    select UserID
    from UserPropertyValues upv1
    inner join UserPropertyValues upv2 on upv1.UserID = upv2.UserID 
        and upv1.PropertyValueID = 1 and upv2.PropertyValueID = 5
) a

Answer 2

You need to join to the key_value store individually for each property you want to get (2 properties, 2 joins, 100 properties, 100 joins), This is why it is a poor design choice for performance.

Answer 3

Expanding on OrbMan's answer. This is going to give you one row per user/property.

SELECT U.UserLogin, U.FirstName, U.LastName, P.Name AS PropertyName, PV.Name AS PropertyValue
FROM Users U
INNER JOIN UserPropertyValues UPV
ON U.ID = UPV.UserID
INNER JOIN Properties P
ON UPV.PropertyID = P.ID
INNER JOIN PropertyValues PV
ON UPV.PropertyID = PV.ID
where UserID in ( 
    select UserID
    from UserPropertyValues upv1
    inner join UserPropertyValues upv2 on upv1.UserID = upv2.UserID 
        and upv1.PropertyValueID IN (@PropertyIDs))

Answer 4

I figured out how to get this working in a completely hacky fashion:

SELECT UserLogin, FirstName, LastName
FROM Users
LEFT OUTER JOIN 
(
    SELECT UserID
    FROM UserPropertyValues 
    WHERE PropertyValueID IN (@PropertyIDs)
    GROUP BY UserID
    HAVING COUNT(UserID) = 
        (
            SELECT COUNT(*) FROM 
            (
            SELECT PropertyID FROM PropertyValues 
            WHERE ID IN (@PropertyIDs) GROUP BY PropertyID
            ) p
        )   
) filtered on Users.UserID = filtered.UserID

Because we can determine the number of properties used in the parameter @PropertyIDs (returned by the select count(*)), we can make sure that the users returned from the query has the same number of properties as what was passed in @PropertyIDs.