Retrieving shared items between users

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Retrieving shared items between users

Author

Topic

karlmcauley
Starting Member

9 Posts

Posted - 2005-09-15 : 08:45:23

I have a table which stores application access levels/menus per user per job title.

For example i have 4 analyst programmers, each with their own subset of menus within 4 different applications. Some of these application menus are common to all users, however some are unique to the user.

I want to devise a piece of SQL to tell me what application menu commonalities there are between users and what the difference are.

There is only one table with this information in.

I have tried linking table to itself and returning only "shared" menus: eg.

SELECT   distinct
    ap.app_title,
    ap.app_id,
    ap.app_user_id, 
    ap.Surname,
    ap.Forename, 
    ap.job_title,
    ap.menu_id,
    ap.Menu, 
    ap.LAC_ID,
    ap.ClockNumber, 
    ap.nt_logon,
   ap. division
FROM         
    tbl_Actual_perms_unique_import ap,
     tbl_Actual_perms_unique_import ap1
where
   (ap.menu <> ap1.menu and ap.surname = ap1.surname and ap.app_title = ap1.app_title)
   
order by 
   ap.Surname,
   ap.Forename,
   ap.app_title

But the above returns commonalites for each user and not all users as a group. E.g.
If person A had app menu 1,2,3,4
Person B had app menu 1,2
Person C had app menu 1,3
Person D had app menu 1

I want to see app menu 1 as my result as this is the only one shared between the group of people.

Any ideas? Any suggesstions whatsoever on a best approach?

Many thanks in advance

Cheers

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-15 : 09:45:41

Try this

select top 1 appmenu, count(*) as mnucount from yourTable group by appmenu order by 2 desc

Madhivanan

Failing to plan is Planning to fail

karlmcauley
Starting Member

9 Posts

Posted - 2005-09-15 : 09:55:02

madhivanan,

thanks for your response, but your sql only returns the first record from the result set and not appmenu commonalites between all users in the group.

any other ideas?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-15 : 09:57:11

I have just edited my reply
See it again

select top 1 appmenu, count(*) as mnucount from yourTable group by appmenu order by 2 desc

Madhivanan

Failing to plan is Planning to fail

karlmcauley
Starting Member

9 Posts

Posted - 2005-09-15 : 10:04:13

madhivanan,

your sql:

select top 1 menu_id, count(1) as mnucount from tbl_Actual_perms_unique_import group by menu_id order by 2 desc

returns:
menu_id mnucount
18 4

But menu_id only appears 3 times under 3 people (not all 4)if i check the data manually or do a distinct count of the dataset for that menu_id.

Very close. Any other suggesstions

Thanks very much.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-15 : 10:18:28

Post some more sample data

Madhivanan

Failing to plan is Planning to fail

karlmcauley
Starting Member

9 Posts

Posted - 2005-09-15 : 10:29:54

madhivanan,

Sorry about that one....whilst i was doing the distinct count i only retrieved three people with that menu_id, but one of the people had the menu item twice.

Person Name Menu_ID Menu Name App_ID
Susan Tweedie 18 WARRMENU 1
Susan Tweedie 18 WARRMENU 1
Stan Haworth 18 WARRMENU 1
Alan West 18 WARRMENU 1

But in the entire dataset there is also another person Paul McNally who does not have this menu_id.

Is there a way to return zero (or another identifier) if all people in a particular dataset do NOT share all menu_id's that exist within that dataset. I have unique id for each person, menu,and application.

Cheers

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-15 : 10:47:31

Try this logic


If ((select top 1 count(1) as mnucount from tbl_Actual_perms_unique_import group by menu_id order by desc))=4 
select top 1 map_id, count(1) as mnucount from tbl_Actual_perms_unique_import group by menu_id order by 2 desc
else
Select 'No matching Records'

Madhivanan

Failing to plan is Planning to fail

karlmcauley
Starting Member

9 Posts

Posted - 2005-09-15 : 11:01:53

madhivanan,

Once again thanks for your response:

The number of people per job title could differ from dataset to dataset. As such i have amended the code you done to come up with:


declare @unique_people_count int

--get unique count of people in dataset
select @unique_people_count = count(distinct clocknumber) 
from tbl_Actual_perms_unique_import

--if we get a menu item where ALL people share then tell me that menu item
If ((select top 1 count(distinct clocknumber) as mnucount 
from tbl_Actual_perms_unique_import 
group by menu_id order by 1 desc))=@unique_people_count 
begin
	select top 1 menu_id, count(1) as mnucount 
        from tbl_Actual_perms_unique_import 
        group by menu_id 
        order by 2 desc
end
else
begin
	Select 0 as 'No matching Records'
end

This works, but is it the best way?

Cheers

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-15 : 11:07:05

There can be better way but I dont know

Madhivanan

Failing to plan is Planning to fail

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-18 : 22:41:32

did you know that you can add a condition to your query with a group by clause?

instead of using 'where', use 'having' like in
having count(*)=@unique_people_count

HTH

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources