Big messy join query with 2 levels of user acces..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Big messy join query with 2 levels of user acces..

Author

Topic

pyrrhus_finch
Yak Posting Veteran

51 Posts

Posted - 2006-02-09 : 19:46:54

Dear friends, I have this query in MS Access wherin i want to list the date, encouter # and event type from multiple tables - and limit that list to where the user has been assigned access to either that event type (where user id has been linked to that event type in [tblUserEvents] OR they have been assigned access the that events notification group in [tblNotification].

The following WORKS fine but its UGLY!

SELECT [Id],[Date], [Encounter] ,'Complaint' AS IncidentType, [Location] FROM tblComplaints WHERE tblComplaints.[Status] = 1 AND
((1 IN (Select tblUserEvents.[Event_Id] from tblUserEvents where tblUserEvents.[User_Id] =GetEmpID() )) OR
tblComplaints.[Id] IN (Select tblNotification.[Incident_ID] from tblNotification WHERE tblNotification.[Dept_ID] IN
(Select tblUserDepts.[Dept_Id] FROM tblUserDepts WHERE tblUserDepts.[User_Id] =GetEmpID() ) AND Incident_Type = 'Complaint'));

UNION ALL SELECT [Id],[Date], [Encounter] ,'DecubitusUlcer' AS IncidentType, [Location] FROM tblDecubitusUlcers WHERE tblDecubitusUlcers.[Status] = 1 AND
((2 IN (Select tblUserEvents.[Event_Id] from tblUserEvents where tblUserEvents.[User_Id] = GetEmpID() )) OR
tblDecubitusUlcers.[Id] IN (Select tblNotification.[Incident_ID] from tblNotification WHERE tblNotification.[Dept_ID] IN
(Select tblUserDepts.[Dept_Id] FROM tblUserDepts WHERE tblUserDepts.[User_Id] =GetEmpID() ) AND Incident_Type = 'DecubitusUlcer'));

UNION ALL SELECT [Id],[Date], [Encounter] ,'Fall' AS IncidentType, [Location] FROM tblFalls WHERE tblFalls.[Status] = 1 AND
((3 IN (Select tblUserEvents.[Event_Id] from tblUserEvents where tblUserEvents.[User_Id] =GetEmpID() )) OR
tblFalls.[Id] IN (Select tblNotification.[Incident_ID] from tblNotification WHERE tblNotification.[Dept_ID] IN
(Select tblUserDepts.[Dept_Id] FROM tblUserDepts WHERE tblUserDepts.[User_Id] =GetEmpID() ) AND Incident_Type = 'Fall'))
ORDER BY [Date];

its a stored query calling on a function to fetch the users id (still can't get it to call a global variable here so the function simply gets that variable)...

I have a feeling I approached writing it the wrong way. I may have to add a half dozen other unions here in the future..
Can someone take a look and tell me if there is a better way to write this and get the same result? Using joins? Or something else?
Thanks a lot!

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2006-02-10 : 05:12:29

Can you supply us with sample input data and matching expected results?

pyrrhus_finch
Yak Posting Veteran

51 Posts

Posted - 2006-02-10 : 18:04:07

ok.. each table has lots various a critea, but here, im just fething what they have in common..

so for example if my data looks like this..

tblComplaints
1, 01/01/05, VA568999
2, 02/15/05, VA869778
...

tblDecubitusUlcer
1, 01/21/05, VA568945
2, 02/25/05, VA869758

....

tblFalls
1, 01/21/05, VA545454
2, 02/21/05, VA869666
2, 02/22/05, VA869666
....

tblNotification = ID, deptID, IncidentID, IncidentType
1,2,1,Complaint
2,2,2,Complaint
3,2,1,DecubitusUlcer
4,2,2,DecubitusUlcer
5,2,1,Fall
6,2,2,Fall
7,2,3,Fall

tblUserDepts = ID, User_ID, Dept_ID
1,1,2
2,2,3
3,1,4
...

tblUserEvents ID, USer_Id, Event_Id
1,1,1
2,1,2
3,1,3

the above query gives me this:

1, 01/01/05, VA568999, Complaint
2, 02/15/05, VA869778, Complaint
1, 01/21/05, VA568945, DecubitusUlcer
2, 02/25/05, VA869758, DecubitusUlcer
1, 01/21/05, VA545454, Fall
2, 02/21/05, VA869666, Fall
3, 02/22/05, VA869666, Fall

if user ID = 1 because the user has been granted access to either the dept where that incident occured.. via tblUserDepts or to the indicent type via tblUserEvents

it all works.. but i need call this where clause for every unioned query:

WHERE tblFalls.[Status] = 1 AND
((3 IN (Select tblUserEvents.[Event_Id] from tblUserEvents where tblUserEvents.[User_Id] =GetEmpID() )) OR
tblFalls.[Id] IN (Select tblNotification.[Incident_ID] from tblNotification WHERE tblNotification.[Dept_ID] IN
(Select tblUserDepts.[Dept_Id] FROM tblUserDepts WHERE tblUserDepts.[User_Id] =GetEmpID() ) AND Incident_Type = 'Fall'))

seems like i might want to put it in the end and only once somehow??

Or is this all fine.. i mean it works.. but i just doubt its the cleanest, most efficient way to do it...

thanks

pyrrhus_finch
Yak Posting Veteran

51 Posts

Posted - 2006-02-22 : 12:14:32

anybody out there? pleease! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources