SQL Server SQL: New column (categories) based on criteria from other table

enxuqcxy 于 2023-03-07 发布在 Go

关注(0)|答案(2)|浏览(159)

I'm trying to create a new table that will act as a page-level security filter on a Power BI report. I have two tables, doc_class and comp_class, which are routinely updated and do all the calculations I need. I simply need a new table that will assign page access based on an OwnerID's presence in the doc_class table.

Any OwnerID in the doc_class table needs access to two Power BI pages: Doctor and Doctor ROI ALL OwnerIDs in the comp_class table need access to a different set of two pages: Practice and Practice ROI. All OwnerIDs in doc_class are also in comp_class, if that makes any difference. Page levels needs to each have their own row, and that is the part that is tripping me up.

Here are example tables (IDs only--the other data does not matter in this use case):

doc_class:      
| OwnerID  |
| -------- |
| 2        |
| 3        |

comp_class:
| OwnerID |
| --------|
| 1       |
| 2       |
| 3       |
| 4       |

Expected result:

| OwnerID  | page         |
| -------- | ------------ |
| 1        | Practice     |
| 1        | Practice ROI |
| 2        | Doctor       |
| 2        | Doctor ROI   |
| 2        | Practice     |
| 2        | Practice ROI |
| 3        | Doctor       |
| 3        | Doctor ROI   |
| 3        | Practice     |
| 3        | Practice ROI |
| 4        | Practice     |
| 4        | Practice ROI |

sql-server

来源：https://stackoverflow.com/questions/75605315/sql-new-column-categories-based-on-criteria-from-other-table

2条答案

按热度按时间

kx5bkwkv1#

SELECT cc.ownerID, 'Practice' page
  FROM comp_class cc
 WHERE NOT EXISTS
          (SELECT 1
             FROM doc_class dc
            WHERE dc.ownerID = cc.ownerID)
UNION ALL
SELECT ownerID, 'Practice ROI' page
  FROM comp_class cc
 WHERE NOT EXISTS
          (SELECT 1
             FROM doc_class dc
            WHERE dc.ownerID = cc.ownerID)
UNION ALL
SELECT dc.ownerID, 'Doctor' page
  FROM doc_class dc
 WHERE EXISTS
          (SELECT 1
             FROM comp_class cc
            WHERE cc.ownerID = dc.ownerID)
UNION ALL
SELECT dc.ownerID, 'Doctor ROI' page
  FROM doc_class dc
 WHERE EXISTS
          (SELECT 1
             FROM comp_class cc
            WHERE cc.ownerID = dc.ownerID)
UNION ALL
SELECT dc.ownerID, 'Practice' page
  FROM doc_class dc
 WHERE EXISTS
          (SELECT 1
             FROM comp_class cc
            WHERE cc.ownerID = dc.ownerID)
UNION ALL
SELECT dc.ownerID, 'Practice ROI' page
  FROM doc_class dc
 WHERE EXISTS
          (SELECT 1
             FROM comp_class cc
            WHERE cc.ownerID = dc.ownerID);

赞(0）回复(0）举报 2023-03-07

aiazj4mn2#

Alternative solution. Union removes duplicates so you can take advantage of that. Also using cross apply makes code easier to read.

-- Any OwnerID in the doc_class table needs access to two Power BI pages: Doctor and Doctor ROI
select OwnerID, [Page] 
from doc_class
    cross apply (
        select 'Doctor' [Page] union all
        select 'Doctor ROI' [Page]
    ) p

UNION

-- ALL OwnerIDs in the comp_class table need access to a different set of two pages: Practice and Practice ROI.
select OwnerID, [Page]  
from comp_class
    cross apply (
        select 'Practice' [Page] union all
        select 'Practice ROI' [Page]
    ) p

赞(0）回复(0）举报 2023-03-07

我来回答

SQL Server SQL: New column (categories) based on criteria from other table

2条答案

相关问题

热门标签

最新问答