Home » Other » Client Tools » Having trouble with this complex query
Having trouble with this complex query [message #25929] Tue, 27 May 2003 09:24 Go to next message
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 Go to previous messageGo to next message
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)
Click here for documentation on the EXISTS clause.

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 Go to previous messageGo to next message
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 Go to previous message
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!
Previous Topic: Finding Largest Among Matrix
Next Topic: How to convert dataTime format?
Goto Forum:
  


Current Time: Thu Mar 28 16:36:29 CDT 2024