January 5, 2014 at 4:48 pm
Hello all,
I am looking to produce a query which will show users in my erp database which shows them as having access to multiple companies.
I have been able to produce a result set with a self join but I have to hardwire it with the specific companies.
If possible I would like it to be more dynamic where it will return all users who have access to a specific company and then show additional companies to which the user has access. Below you will find the query's I've tried and sample data to create a table and test.
--This query will return both user who have access to our primary company and subsidiaries
--It returns users who only have access to one company. I need to show users who have access
--to multiple companies
select * from [dbo].[Member Of] where Company in ('Main Company', 'China Company')
--This query returns no results.
select * from [dbo].[member of] where Company = 'Main Company' and company = 'China Company'
--Here is the self join which works but isn't very dynamic as I have to change the other company
select MO1.[User ID]
, MO1.Company
, MO2.Company as Company2
--, MO3.Company as Company3
from [dbo].[Member Of] as MO1--, [dbo].[Member Of] as MO2, [dbo].[Member Of] as MO3
left outer join [dbo].[Member Of] as MO2 on MO2.[User ID] = MO1.[User ID]
where MO1.Company = 'Main Company' and MO1.[Group ID] = 'ALL' and MO2.Company = 'China Company' and MO2.[Group ID] = 'ALL'
order by [User ID]-- and Company = 'GK Singapore 2012'
Below you will find some sample data
CREATE TABLE #MemberOf
(
[User ID] [varchar](20) NOT NULL,
[Group ID] [varchar](20) NOT NULL,
[Company] [varchar](30) NOT NULL
)
Insert into #MemberOf
(
[User ID],[Group ID],[Company]
)
Values
('Amy','ALL','Main Company')
,('Amy','700','Main Company')
,('Amy','ALL','China Company')
,('Amy','700','China Company')
,('Amy','ALL','India Company')
,('Amy','700','India Company')
,('Bob','ALL','Main Company')
,('Bob','ALL','Canada Company')
,('Bob','ALL','India Company')
,('Bob','ALL','UK Company')
,('Bob','700','Main Company')
,('Bob','700','Canada Company')
,('Bob','700','India Company')
,('Bob','700','UK Company')
,('Bob','800','Main Company')
,('Bob','800','Canada Company')
,('Bob','800','India Company')
,('Bob','800','UK Company')
,('Chris','ALL','Main Company')
,('Dan','ALL','Main Company')
,('Dan','ALL','China Company')
,('Dan','ALL','India Company')
,('Erin','ALL','Main Company')
,('Frank','ALL','China Company')
,('Gary','ALL','India Company')
,('Hank','ALL','Main Company')
,('Hank','ALL','UK Company')
,('Chris','700','Main Company')
,('Dan','700','Main Company')
,('Dan','700','China Company')
,('Dan','700','India Company')
,('Erin','700','Main Company')
,('Frank','700','China Company')
,('Gary','700','India Company')
,('Hank','700','Main Company')
,('Hank','700','UK Company')
Select * from #MemberOf
The [Group ID] column is a permission that grants access to different modules of the application so it may not be needed. I only need to return 1 record of data per company
What would be the best way to show only user who have access to multiple companies and list the companies?
Something that would look like the below result set or showing additional columns for each company in one record.
UserID GroupID Company
Amy ALL Main Company
Amy ALL China Company
Amy ALL India Company
Bob ALL Main Company
Bob ALL Canada Company
Bob ALL India Company
Dan ALL Main Company
Dan ALL China Company
Dan ALL India Company
Hank ALL Main Company
Hank ALL UK Company
Any help would be appreciated.
Keith
January 5, 2014 at 5:03 pm
How does this go?
select a.*
from #memberof a
inner join (select
from #memberof
group by
having count(*) > 1) b
on a. = b.
order by a.
January 5, 2014 at 5:52 pm
Ultimately, I believe you will need to aggregate twice. Once on User ID and once on company. So here's an alternate approach.
SELECT a.[User ID], [Group ID], Company
FROM
(
SELECT [User ID], [Group ID]=MAX([Group ID])
FROM #MemberOf
GROUP BY [User ID]
HAVING COUNT(DISTINCT Company) > 1
) a
CROSS APPLY
(
SELECT [User ID], Company
FROM #MemberOf b
WHERE a.[User ID] = b.[User ID]
GROUP BY [User ID], Company
) b
ORDER BY a.[User ID], Company;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 5, 2014 at 6:23 pm
This will give you a comma separated list of company names for each user id/group id combination
SELECT
[User ID],[group id]
,VenNum_B = STUFF((Select ',' + CAST(Company AS VARCHAR(1000))
FROM #MemberOf t2
WHERE t1.[User Id] = t2.[User Id] AND t1.[group id] = t2.[group id]
FOR XML PATh ('')
),1,1,'')
FROM #MemberOf t1
GROUP BY t1., t1.[group id]
User IDgroup idVenNum_B
Amy700Main Company,China Company,India Company
AmyALLMain Company,China Company,India Company
Bob700Main Company,Canada Company,India Company,UK Company
Bob800Main Company,Canada Company,India Company,UK Company
BobALLMain Company,Canada Company,India Company,UK Company
Chris700Main Company
ChrisALLMain Company
Dan700Main Company,China Company,India Company
DanALLMain Company,China Company,India Company
Erin700Main Company
ErinALLMain Company
Frank700China Company
FrankALLChina Company
Gary700India Company
GaryALLIndia Company
Hank700Main Company,UK Company
HankALLMain Company,UK Company
January 8, 2014 at 5:42 am
Thanks for the post dogramone but I am hoping to have only 1 record per company show in the result whereas this query returns the additional group id's. I could use this but would end up having to manipulate the data in excel to be cleaner.
dogramone (1/5/2014)
How does this go?select a.*
from #memberof a
inner join (select
from #memberof
group by
having count(*) > 1) b
on a. = b.
order by a.
January 8, 2014 at 5:44 am
Nice Dwain. This definitely works. I didn't think to use CROSS APPLY. Thanks for the post.
dwain.c (1/5/2014)
Ultimately, I believe you will need to aggregate twice. Once on User ID and once on company. So here's an alternate approach.
SELECT a.[User ID], [Group ID], Company
FROM
(
SELECT [User ID], [Group ID]=MAX([Group ID])
FROM #MemberOf
GROUP BY [User ID]
HAVING COUNT(DISTINCT Company) > 1
) a
CROSS APPLY
(
SELECT [User ID], Company
FROM #MemberOf b
WHERE a.[User ID] = b.[User ID]
GROUP BY [User ID], Company
) b
ORDER BY a.[User ID], Company;
January 8, 2014 at 5:56 am
I like this idea of using the stuff function and delimiting the company's in the same column. I think with this and dwain's suggestion I'll be able to produce a result set that is very tidy!
Thanks for your input.
Christian Graus (1/5/2014)
This will give you a comma separated list of company names for each user id/group id combination
SELECT[User ID],[group id]
,VenNum_B = STUFF((Select ',' + CAST(Company AS VARCHAR(1000))
FROM #MemberOf t2
WHERE t1.[User Id] = t2.[User Id] AND t1.[group id] = t2.[group id]
FOR XML PATh ('')
),1,1,'')
FROM #MemberOf t1
GROUP BY t1., t1.[group id]
User IDgroup idVenNum_B
Amy700Main Company,China Company,India Company
AmyALLMain Company,China Company,India Company
Bob700Main Company,Canada Company,India Company,UK Company
Bob800Main Company,Canada Company,India Company,UK Company
BobALLMain Company,Canada Company,India Company,UK Company
Chris700Main Company
ChrisALLMain Company
Dan700Main Company,China Company,India Company
DanALLMain Company,China Company,India Company
Erin700Main Company
ErinALLMain Company
Frank700China Company
FrankALLChina Company
Gary700India Company
GaryALLIndia Company
Hank700Main Company,UK Company
HankALLMain Company,UK Company
January 8, 2014 at 6:12 am
Here's a proposal which reads the source table only once (you didn't see that table spool)
SELECT
[User ID], [Group ID], [Company]
FROM (
SELECT
[User ID], [Group ID] = MAX([Group ID]), [Company],
ct = COUNT(*) OVER(PARTITION BY [User ID])
FROM #MemberOf
GROUP BY [User ID], [Company]
) d
WHERE ct > 1
ORDER BY [User ID], [Company]
And here's another just for fun:
SELECT
[User ID], [Group ID], [Company]
FROM (
SELECT
[User ID], [Group ID], [Company],
ct = COUNT(*) OVER (PARTITION BY [User ID])
FROM (
SELECT
[User ID], [Group ID], [Company],
rn = ROW_NUMBER() OVER (PARTITION BY [User ID], dr ORDER BY [Group ID] DESC)
FROM (
SELECT
[User ID], [Group ID], [Company],
dr = DENSE_RANK() OVER (PARTITION BY [User ID] ORDER BY [Company])
FROM #MemberOf
) d
) d2
WHERE rn = 1
) d3
WHERE ct > 1
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 8, 2014 at 6:48 am
Nice Chris.
January 8, 2014 at 7:05 am
Smash125 (1/8/2014)
Nice Chris.
Sometimes 😀
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
January 8, 2014 at 12:30 pm
Very nice, beautifully formatted, simple to understand and hopefully the op will say meats needs perfectly
January 8, 2014 at 12:51 pm
Very cool. Thanks Chris. Anything that improves efficiency at different levels is ideal. It's amazing how many ways there are to get the result set. I need to build access lists on the fly for things such as inventory lockdowns and when I have over 1,500 users it can be rather tedious. This saves me a great deal of time!
Thanks all!
ChrisM@Work (1/8/2014)
Here's a proposal which reads the source table only once (you didn't see that table spool)
SELECT[User ID], [Group ID], [Company]
FROM (
SELECT
[User ID], [Group ID] = MAX([Group ID]), [Company],
ct = COUNT(*) OVER(PARTITION BY [User ID])
FROM #MemberOf
GROUP BY [User ID], [Company]
) d
WHERE ct > 1
ORDER BY [User ID], [Company]
And here's another just for fun:
SELECT[User ID], [Group ID], [Company]
FROM (
SELECT
[User ID], [Group ID], [Company],
ct = COUNT(*) OVER (PARTITION BY [User ID])
FROM (
SELECT
[User ID], [Group ID], [Company],
rn = ROW_NUMBER() OVER (PARTITION BY [User ID], dr ORDER BY [Group ID] DESC)
FROM (
SELECT
[User ID], [Group ID], [Company],
dr = DENSE_RANK() OVER (PARTITION BY [User ID] ORDER BY [Company])
FROM #MemberOf
) d
) d2
WHERE rn = 1
) d3
WHERE ct > 1
January 8, 2014 at 5:07 pm
ChrisM@Work (1/8/2014)
..(you didn't see that table spool)
...
I did.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply