Where the join table clause is used for user-defined key / value pairs

Question

Our application allows administrators to add "User Properties" so that they can adapt the system to their own personnel management systems. For example, if your company has departments, you can define “Departments” in the “Properties” table, and then add values ​​corresponding to “Departments”, such as “Jewelry”, “Electronics”, etc. Then you can assign a department to users,

Here is the diagram:
_{(source: mindgravy.net )}

In this scheme, a user can have only one UserPropertyValue for each property, but should not have a value for the property.

I am trying to create a query that will be used in SSRS 2005, and also use PropertyValues ​​as a 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. I would like this query to return users with BY PROPERTY values.

Thus, if PropertyValueID = 1 refers to the Department (Jewelry), and PropertyValueID = 5 refers to the EmploymentType (Full-time), I want to return all users who are in the Jewelry Department and have the status of EmployeeType full-time, can this be done?

---

Here is a complete example of the data:

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

A,

:

, SSRS, , , @PropertyIDs, SSRS.

WHERE UPV.PropertyValueID IN (@PropertyIDs)

Answer 1

, :

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

, @PropertyID ( select (*)), , , , , , @PropertyIDs.

Answer 2

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 3

key_value , (2 , 2 , 100 , 100 ). .

Answer 4

OrbMan. /.

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