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

Our application allows administrators to add “User Properties” in order for them to be able to tailor the system to match their own HR systems. For example, if your company has departments, you can define “Departments” in the Properties table and then add values that correspond to “Departments” such as “Jewelry”, “Electronics” etc… You are then able to assign a department to users.

Here is the schema:

_{(source: mindgravy.net)}

In this schema, a User can have only one UserPropertyValue per Property, but doesn’t have to have a value for the pr开发者_运维知识库operty.

I am trying to build a query that will be used in SSRS 2005 and also have it use the PropertyValues as the filter for users. My query looks like this:

SELECT UserLogin, FirstName, LastName
FROM Users U
LEFT OUTER JOIN UserPropertyValues UPV
    ON U.ID = UPV.UserID
WHERE UPV.PropertyValueID IN (1, 5)

When I run this, if the user has ANY of the property values, they are returned. What I would like to have is where this query will return users that have values BY PROPERTY.

So if PropertyValueID = 1 is of Department (Jewelry), and PropertyValueID = 5 is of EmploymentType (Full Time), I want to return all users that are in Department Jewelry that are EmployeeType of Full Time, can this be done?

---

Here's a full data example:

• User A has Department(Jewelry value = 1) and EmploymentType(FullTime value = 5)
• User B has Department(Electronics value = 2) and EmploymentType(FullTime value = 5)
• User C has Department(Jewelry value = 1) and EmployementType(PartTime value = 6)

My query should only return User A using the above query

UPDATE:

I should state that this query is used as a dataset in SSRS, so the parameter passed to the query will be @PropertyIDs and it is defined as a multi-value parameter in SSRS.

WHERE UPV.PropertyValueID IN (@PropertyIDs)

---

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.

---

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

---

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.

---

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))