Where clause on joined table used for user defined key/value pairs
- by Steve Wright
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:
In this schema, a User can have only one UserPropertyValue per Property, but doesn’t have to have a value for the property.
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)
