Re: help me join these tables

Mitch,

Given that I'm not 100% sure what question you're asking, forgive me if this seems a little off.

[Quoted] Under the tables as shown:

A Screen may have a Role that isn't in the system (screen 4 is assigned to [Quoted] Role 1, but is not in tblScreen).
A User may have a Screen that isn't in the system (screen 6 is assigned to User 88, but is not in tblScreen, and screen 5 is assigned to User 100, but is not in tblScreen, too).

Therefore, I would:

In tblUser_Access change Screen_ID to Role_ID (Roles are now be assigned to Users).

Create tblCnfgRoles_Access (configured roles) with Screen_ID and Role_ID columns (both are the PK), and load which Roles have which Screens.

Establish RI from tblScreen.Screen_ID to tblCnfgRoles_Access.Screen_ID

Drop the Screen_ID column from tblRole_Access.

Remove Duplicate entries in tblRole_Access.

Establish RI from tblRole_Access.Role_ID to tblCnfgRoles_Access.Role_ID

Establish RI from tblRole_Access.Role_ID to tblUser_Access.Role_ID

At this point:

Users have Roles, and can't have a Role that isn't in the system.

Roles have Screens, and can't have a Screen that isn't in the system.

Make sure all Roles have the proper Screens, and everything should be set.

"Mitch Abaza" <mitch_at_NOSPAMmabaza.com> wrote in message news:pUBC8.8946$fH5.8551976_at_kent.svc.tds.net...
> I'm no novice when it cames to complex SQL, but I'm having a hell of a
time
> getting these tables to join.
>
> I have a *large* list of users and their security needs for an application
I
> wrote. I'm trying to pare the list down to just the users whose security
> profile (tblUser_Access) matches an existing security roles
> (tblRole_Access). I can't join only on tblUser_Access.Screen_ID =
> tblRole_Access.Screen_ID, because the same Screen_ID can be part of
multiple
> Roles.
> I need to join on Screen_ID where the Role_ID is the same for all records
> matched. I imagine GROUP BY and several subqueries are required but I
can't
> get any further than that.
> Please help, I've been working on this for way too long.
>
>
> * tblUser_Access *
> UserName User_ID Screen_ID
> =================================
> Mitch 91 4
> Mitch 91 3
> Mitch 91 1
> Mitch 91 2
> Jack 100 5
> Jack 100 3
> Mary 88 6
> =================================
>
> * tblRole_Access *
> =================================
> RoleName Role_ID Screen_ID
> =================================
> Data Entry 1 4
> Data Entry 1 3
> Data Entry 1 2
> Data Entry 1 1
> AP Clerk 2 7
> AP Clerk 2 2
> AP Clerk 2 3
> =================================
>
> *tblScreen*
> =================================
> Screen_ID Screen_Name
> =================================
> 1 Accts Payable
> 2 Accts Receivable
> 3 GL
> 7 Customer
> =================================
>
>
>
Received on Fri May 10 2002 - 10:10:10 CEST