Complex? Insert Into Query

[jchastai]

I have a table of permissions for a series of objects (i.e. user-1 has permission level 4 to calendar-1) as follows (partial table).

---------------------

Permission_ID   Handle       User     Group     pLevel
-------------   ----------   -------  --------  ---------
15              Calendar-1   User-1             4
54              Calendar-1            Group-2   2
50              Calendar-1   User-5             3
16              Folder-1     User-1             4
60              Folder-1     User-5             2

---------------------

I am needing to do an Insert Query with a new Handle called file-1. The problem is I want to add in a super-set of the permissions for both calendar-1 and folder-1 ... i.e.

Handle       User     Group     pLevel
----------   -------  --------  ---------
File-1       User-1             4
File-1                Group-2   2
File-1       User-5             3

Basically I want the highest permission level for each user/group that has permission for either calendar-1 and/or folder-1 .... and put that list back into the permission table for the new handle file-1.

Clear as mud huh?

I would use the following query if I were just copying permissions from folder-1 to file-2.

INSERT INTO Unit_Permissions (Handle, User, [Group], pLevel)
SELECT 'File-1' AS Handle, User, Group, pLevel
FROM Unit_Permissions
WHERE Handle = 'Folder-1'

The problem is that I want to pull the superset of permissions from both folder-1 and calendar-1 and put them in as the permissions for file-1.

Anybody have any great ideas on how to twist SQL around to do this?

Thanks

 

[tlbroadbent]

This should work for you.

INSERT INTO
Unit_Permissions (Handle, [User], [Group], pLevel)
SELECT
'File-1' AS Handle, p.User,
p.Group, Max(p.pLevel) AS MaxLevel
FROM Unit_Permissions AS p
GROUP BY 'File-1', p.User, p.Group; Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[jchastai]

That was the answer, thanks.