Re: help me join these tables

give us an example of what you expect to see in result vs. what not.

Mikhail Berlyant
Data Integrator, Data Systems
Launch Your Yahoo!Music Experience http://launch.yahoo.com Brainbench MVP for Visual Basic www.brainbench.com

"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 Thu May 09 2002 - 16:44:47 CDT