help me join these tables

From: Mitch Abaza <mitch_at_NOSPAMmabaza.com>
Date: Thu, 09 May 2002 21:31:01 GMT
Message-ID: <pUBC8.8946$fH5.8551976_at_kent.svc.tds.net>

[Quoted] [Quoted] I'm no novice when it cames to complex SQL, but I'm having a hell of a time [Quoted] getting these tables to join.

[Quoted] I have a *large* list of users and their security needs for an application I [Quoted] wrote. I'm trying to pare the list down to just the users whose security [Quoted] 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.
[Quoted] [Quoted] 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.
[Quoted] [Quoted] 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 Thu May 09 2002 - 23:31:01 CEST