| Having trouble with this complex query [message #25929] |
Tue, 27 May 2003 09:24  |
Rabeeha Khan
Messages: 11
Registered: May 2003
|
Junior Member |
|
|
I initially thought this would not be complicated but for some reason I cannot get how to word this query.
There are three tables, one that contains user pedigree information, one that contains information about entitlements that exist and which templates they belong to, and one that contains information about which entitlements a user has. I need to find out which users do not have access to any one of the entitlements in a particular template. This is what I am confused about. I know in a sub-select to search for a case where any one might exist, but how do I search for a situation where all should exist and any one might be missing? Sorry if this sounds confusing but this is the best I can explain and I've been racking my brain on it for a while.
in pseudocode, i come up with somethign like this
select users from
user_info_table a, user_entitlement_info_table b
where a.user_id=b.user_id
and the users have an entitlement missing from
(select all entitlements from entitlements_table where template='A');
Hope this makes sense. The part of entitlement missing from the following list is the part I'm having trouble with. Please help.
Thanks.
|
|
|
|
| Re: Having trouble with this complex query [message #25930 is a reply to message #25929] |
Tue, 27 May 2003 11:44  |
Art Metzer
Messages: 2480
Registered: December 2002
|
Senior Member |
|
|
If I understand your data model correctly, Rabeeha, then you would code something like SELECT users
FROM user_info_table u
, user_entitlement_info_table ue
WHERE u.user_id = ue.user_id
AND NOT EXISTS (SELECT NULL
FROM entitlements_table e
WHERE e.template = 'A'
AND e.entitlement_id = ue.entitlement_id)
Good luck, Rabeeha.
A
|
|
|
|
| Re: Having trouble with this complex query [message #25931 is a reply to message #25930] |
Tue, 27 May 2003 12:10 |
Rabeeha Khan
Messages: 11
Registered: May 2003
|
Junior Member |
|
|
Thanks for the help art. I tried to construct a similar query with a slight modification but it results in a list of entitlements that the user already has. I am looking for the ones that the user is missing:
select a.web_user_id, function_group_id
from euser_profile a, euser_function_group b
where a.user_id=b.user_id
and NOT EXISTS
(select NULL from euserclass_template_details c
where c.userclass_id='INTERNAL_LEHMAN'
and c.function_group_id=b.function_group_id);
|
|
|
|
| Re: Having trouble with this complex query [message #25934 is a reply to message #25929] |
Tue, 27 May 2003 14:04 |
Rabeeha Khan
Messages: 11
Registered: May 2003
|
Junior Member |
|
|
I actually got a list of the users that have missing entitlements from this query:
select a.web_user_id, count(*)
from euser_profile a,
euser_function_group b,
(select function_group_id from euserclass_template_details
where userclass_id='INTERNAL_LEHMAN') c
where pk_misc_procs.IS_INTERNAL_USER_ONLY_BOUND(a.user_id)='Y'
and a.user_id=b.user_id
and b.function_group_id=c.function_group_id
group by a.web_user_id
having count(*) < (select count(*) from euserclass_template_details where userclass_id='INTERNAL_LEHMAN');
If someone could help me with how to get the actual entitlements (function groups) that these users are missing, it would be greatly appreciated because i'm stuck!
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
