Case when select

Hi,

I have security table having user name and productLine they have access. When user have access to all productLine, instead of adding many rows just one row with 'ALL' is added. When the user have access to few productLine then rows are added in Security table

example security table
UserID productLine
XXX------All
YYY------Shoes
YYY------bags
YYY------Accessories
ZZZ-----Bags
BBB-----All

SELECT case when productLine = 'All' then (SELECT distinct productLine from dbo.productDimtable)
ELSE productLine END
FROM [Securitytable] S WHERE S.userID = 'XXX'

For user XXX and BBB, it needs to display all the productline in the product table

but it is giving error - Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Any help is appreciated.

thanks

SELECT oa1.productLine
FROM [Securitytable] S 
CROSS APPLY (
    SELECT S.productLine
    WHERE S.productLine <> 'All'
    UNION ALL
    SELECT DISTINCT P.productLine
    FROM dbo.productDimtable P
    WHERE S.productLine = 'All'
) AS oa1
WHERE S.userID = 'XXX'

Thanks Scott, it really helped. But I have additional requirement now,

Instead of getting productLine, user wants productfamily from productDimtable.

SELECT distinct productfamily from [Securitytable] S inner join productDimtable p on s.productLine = p.ProductLine WHERE S.userID = 'XXX' AND S.productLine <> 'All'

Now how to add both these queries
SELECT DISTINCT P.productfamily
FROM dbo.productDimtable P
LEFT OUTER Join [Securitytable] S
ON S.productLine = 'All' WHERE S.userID = 'XXX'

Thanks in advance.

SELECT DISTINCT oa1.productFamily
FROM [Securitytable] S 
CROSS APPLY (
    SELECT DISTINCT p.productFamily
    FROM productDimtable p
    WHERE S.productLine <> 'All' AND
        s.productLine = p.ProductLine
    UNION ALL
    SELECT DISTINCT P.productFamily
    FROM dbo.productDimtable P
    WHERE S.productLine = 'All'
) AS oa1
WHERE S.userID = 'XXX'
ORDER BY productFamily

thanks Scott, it worked prefect. Sure I will read more about Cross Apply

hi

i tried to do this

this is a different way to do then Scotts solution

something different .. i am killing time
hope it helps :slight_smile: :slight_smile:

drop create data ...
DROP TABLE #security 

go 

CREATE TABLE #security 
  ( 
     userid      VARCHAR(100), 
     productline VARCHAR(100) 
  ) 

go 

INSERT INTO #security 
SELECT 'XXX', 
       'All' 

INSERT INTO #security 
SELECT 'YYY', 
       'Shoes' 

INSERT INTO #security 
SELECT 'YYY', 
       'Bags' 

INSERT INTO #security 
SELECT 'YYY', 
       'Accessories' 

INSERT INTO #security 
SELECT 'ZZZ', 
       'Bags' 

INSERT INTO #security 
SELECT 'BBB', 
       'All' 

go 

DROP TABLE #product 

go 

CREATE TABLE #product 
  ( 
     productline VARCHAR(100), 
  ) 

go 

INSERT INTO #product 
SELECT 'Shoes' 

INSERT INTO #product 
SELECT 'Bags' 

INSERT INTO #product 
SELECT 'Accessories' 

go 

SELECT * 
FROM   #security 

go 

SELECT * 
FROM   #product 

go

image

SQL .. in a different way
SELECT s.userid, 
       c.productline 
FROM   #security s 
       JOIN (SELECT productline, 
                    'All' AS ok 
             FROM   #product) c 
         ON s.productline = c.ok 
             OR s.productline = c.productline 
ORDER  BY s.userid, 
          c.productline

image