Greetings,

Table structure

test_users

Data:

role user
12 James
23 James
1002 James
9000 James
50000 James
9000 Scott
23 Brian
9000 Brian
50000 Sushil
45 Moore

I have this enhancement where I have to EXCLUDE all users with roles <
1000 but keeping following in mind
1) role < 1000 and user should not be in any other role
OR
2) role < 1000 and role BETWEEN 9000 and 9010 and user should not be
in any other role.

So using above sample data, we should only receive one user i.e Moore
to be excluded using above logic (as his role is < 1000 and satisfy
aboove two requirements)

This is driving me crazy since yesterday. Whatever I do, it someway or
the other violates the requirement and I end up having incorrect
result. I just need to know how to come up with a query to EXCLUDE
users using above logic.

Any help would be greatly appreciated.

Pankaj says...

These two conditions seem to clash with each other, everyone that meets
the second criteria seem to have been covered by the first one.

GM

On Nov 10, 5:28*pm, Geoff Muldoon <geoff.muld... (AT) trap (DOT) gmail.com>
wrote:
Thanks Geoff. But user Moore becomes eligible for the first
requirement (his role < 1000 and does not have any other role). and
not for second (role < 1000 and does not have any role between 9000
and 9010).

Please correct me if I am wrong?

In article <dc224e63-918d-4755-a6c5-bfa67fa035b9
@h2g2000vbd.googlegroups.com>, Pankaj says...
Moore currently only has a role of 45, so yes, he is eligible under the
first requirement.

If Moore also had a role of 9001, he would NOT be eligible under the FIRST
requirement (even though he would be eligible under the second
requirement) because the roles 9000-9010 are still part of "any other
role".

What are you trying to actually get? Users that have roles that are only
between 0-1000 and 9000-9010?

GM

On Nov 10, 6:34*pm, Geoff Muldoon <geoff.muld... (AT) trap (DOT) gmail.com>
wrote:
Geoff,

Thats exactly what seems to be the dilemma here. I would explain
again. We have set of users

1) with ONLY role < 1000
2) with ONLY (role < 1000 and BETWEEN 9000 and 9010)

The requirement is to remove the users falling in above requirement,
so following users would be excluded

Should be EXCLUDED

role User
23 James
24 James
90 James
23 Scott
9001 Scott

But following user's should NOT BE EXCLUDED as they also belong to
other roles apart from what has been described in above two points.

Should be INCLUDED

role User
23 James1
1002 James1 --Should be included as it belongs to role other than
< 1000 and between 9000 and 9010
9000 James1
9000 Scott1 -- Should be included as it is only part of between
9000 and 9010 and is not < 1000
1000 Brian1
1006 Brian2

I hope I was able to make myself more clearer.

Pankaj says...

Create a sub-query:

select user,
sum(case when <less than 1000 condition>
then 1 else 0
end) as count_under_1000,
sum(case when <between 9000 and 9010 condition>
then 1 else 0
end) as count_special_val,
count(role) as count_all
from <table>
group by user

Then select the records from this sub-query to match your logic based on
the numbers in those calculated value columns.

GM

GM

On Nov 10, 8:16*pm, Geoff Muldoon <geoff.muld... (AT) trap (DOT) gmail.com>
wrote:
Geoff, this worked. Thanks a lot. Take care