Query blues... Help!

I have a security app that is like this: a Facility has many Apps and those
Apps may have any combination Modules, Roles, or Privileges.
Problem Query: Put together a query(s) that will delete all records related
to a single Facility.
CREATE TABLE Applications
(Appl_ID int IDENTITY,
FacilityID char(4) NOT NULL,
ApplName char(200) NOT NULL,
CONSTRAINT PK_Applications PRIMARY KEY CLUSTERED (Appl_ID))
go
CREATE TABLE ModulesRolesPrivileges
(MRP_ID int IDENTITY,
MRP_Name char(150) NOT NULL,
MRP_Type int NOT NULL, -- 1=Role 2=Module 3=Privilege
CONSTRAINT PK_ModulesRolesPrivileges PRIMARY KEY CLUSTERED (MRP_ID))
go
CREATE TABLE ApplicationsModulesRolesPrivileges -- Intersection table
between Applications and ModulesRolesPrivileges
(AMRP_ID int IDENTITY,
Appl_ID int NOT NULL,
MRP_ID int NOT NULL,
CONSTRAINT PK_ApplicationsModulesRolesPrivileges PRIMARY KEY CLUSTERED
(AMRP_ID),
CONSTRAINT R_1 FOREIGN KEY (Appl_ID) REFERENCES Applications (Appl_ID),
CONSTRAINT R_2 FOREIGN KEY (MRP_ID) REFERENCES ModulesRolesPrivileges
(MRP_ID))
go
CREATE TABLE ModulesRolesPrivilegesHierarchy
(MRPH_ID int IDENTITY,
Parent_AMRP_ID int NOT NULL,
Child_AMRP_ID int NOT NULL,
CONSTRAINT PK_ModulesRolesPrivilegesHierarchy PRIMARY KEY CLUSTERED (MRPH_ID),
CONSTRAINT R_3 FOREIGN KEY (Parent_AMRP_ID) REFERENCES
ModulesRolesPrivileges (MRP_ID),
CONSTRAINT R_4 FOREIGN KEY (Child_AMRP_ID) REFERENCES ModulesRolesPrivileges
(MRP_ID))
INSERT Applications (FacilityID, ApplName) VALUES ('0902', 'Host
Financials') -- 1
INSERT Applications (FacilityID, ApplName) VALUES ('0902', 'PeopleSoft')
-- 2
INSERT Applications (FacilityID, ApplName) VALUES ('0902', 'Acct Recv')
-- 3
INSERT Applications (FacilityID, ApplName) VALUES ('0914', 'Acct Recv')
-- 4
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Budget', 2)
-- 1
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('GL', 2)
-- 2
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Supervisor', 1)
-- 3
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Admin', 1)
-- 4
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Query', 3)
-- 5
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Update', 3)
-- 6
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Delete', 3)
-- 7
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Supervisor', 1)
-- 8
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Delete', 3)
-- 9
INSERT ApplicationsModulesRolesPrivileges (Appl_ID, MRP_ID) VALUES (1, 1) -- 1
INSERT ApplicationsModulesRolesPrivileges (Appl_ID, MRP_ID) VALUES (1, 2) -- 2
INSERT ApplicationsModulesRolesPrivileges (Appl_ID, MRP_ID) VALUES (2, 3) -- 3
INSERT ApplicationsModulesRolesPrivileges (Appl_ID, MRP_ID) VALUES (4, 8) -- 4
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (1, 3) -- 1
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (3, 5) -- 2
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (3, 6) -- 3
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (3, 7) -- 4
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (2, 4) -- 5
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (4, 5) -- 6
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (2, 3) -- 7
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (8, 9) -- 8
Is Stored Procedure out of the question? If not, you can do the following.
1) SP will have one argument for FacilityID.
2)
Delete *
From ModulesRolesPrivilegesHierarchy
Where Parent_AMRP_ID IN (SELECT AMRP_ID
FROM ApplicationsModulesRolesPrivileges
WHERE Appl_ID IN (SELECT Appl_ID
FROM Applications
WHERE FacilityID = @.FacilityID))
3)
Delete *
From ApplicationsModulesRolesPrivileges
WHERE Appl_ID IN (SELECT Appl_ID
FROM Applications
WHERE FacilityID = @.FacilityID)
4)
Delete *
From Applications
WHERE FacilityID = @.FacilityID
That should do the trick.
Hope that helps.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
|||Please ignore my answer, I misread table statements. So my answer is
incorrect.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Mohit K. Gupta" wrote:

> Is Stored Procedure out of the question? If not, you can do the following.
> 1) SP will have one argument for FacilityID.
> 2)
> Delete *
> From ModulesRolesPrivilegesHierarchy
> Where Parent_AMRP_ID IN (SELECT AMRP_ID
> FROM ApplicationsModulesRolesPrivileges
> WHERE Appl_ID IN (SELECT Appl_ID
> FROM Applications
> WHERE FacilityID = @.FacilityID))
> 3)
> Delete *
> From ApplicationsModulesRolesPrivileges
> WHERE Appl_ID IN (SELECT Appl_ID
> FROM Applications
> WHERE FacilityID = @.FacilityID)
> 4)
> Delete *
> From Applications
> WHERE FacilityID = @.FacilityID
> That should do the trick.
> Hope that helps.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005