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