时间戳

首页 > 解决方案 > tricky WHERE clause constraint; filter by one fields value but not omit other possible values

sql - tricky WHERE clause constraint; filter by one fields value but not omit other possible values

问题描述

I have dimension tables Person, Position, Department and a fact table Job. Job marries a position to a person. A person can have more than one job and in more than one department. Need to provide user ability to filter by department – however, if I simply constraint results WHERE dept_id IN (‘MATH’), then I’m not representing an accurate picture; forfeiting an employee’s possible other jobs in other departments. I need to the ability to filter by one department and still surface records of all positions in all departments. Wondering if I should create a bridge table. I will be reporting from the Data Warehouse and SSAS Tabular model so im trying to work the DAX as well as the SQL

标签： sqlreporting-servicesdax

解决方案

您可以使用相关子查询来选择该部门中的所有人员，而不限制返回的实际部门：

...
WHERE person_id IN(
    SELECT DISTINCT person_id
    FROM Person p
        JOIN Department d
            ON p.department_id = d.department_id
    WHERE d.dept_id = 'MATH'
    )

推荐阅读