SQL subsets

[Stacy in Savannah]

I have a set of Permitees 12,000+, who own one or more items 58,908 of a list
of 29 items. Some Permitees are only allow to own 3 of these 29 items,
others can own 1-29. I need a SQL that will return DISTINCT permitees that
own 1-3 of those ONLY allowable items. Would anyone help guide me toward a
proper solution?

THANK YOU

 

[Bob Barrows]

I have a set of Permitees

I initially read that as Pharisees ...

12,000+, who own one or more items 58,908
of a list of 29 items. Some Permitees are only allow to own 3 of
these 29 items, others can own 1-29. I need a SQL that will return
DISTINCT permitees that own 1-3 of those ONLY allowable items. Would
anyone help guide me toward a proper solution?

We'll need more information ... specifically, how to identify the
Permitees that are restricted to 3 of those 29 items. Oh! And how to
identify the restricted items ... Is there an item master table
containing a field that identifies the restricted items? Is there a
Permitee master table containing a field that identifies the restricted
Permitees?

 

[StacyC]

Beginnng from the bottom:
No, there is no field that identifies restricted permittes
Yes, there is a Master Table of of Items, but they are not segregated by
restriction type.
The Restricted items are simply a set of chosen numbers (the FK in
tblPermitDetails).

If you'll allow me...
Imagine with the standard HR database: You have employees that are
quailified to work in multiple departments ( Dept. 20, 30, 40, 60, 90) and i
want All Employees that are qualified to work in ONLY Dept's 30 and 90, NO
ONE else, even those that can work in 30, 90, and other dept. I just want
the ones that are owners of those 2 dept. training codes.

Does this help??
Thank you.

 

[KARL DEWEY]

Try this using your table and field names --
SELECT Permitee
FROM YourTable
GROUP BY YourTable.Permitee
HAVING (((Count(YourTable.[Items]))<=3));

 

[Bob Barrows]

I'm sorry, but I'm not looking over your shoulder at your puter screen


All I know so far is you have a table called tblPermitItems. What are
the relevant fields in this table? Does this table maintain the link
between permitees and items? Hmm ... does the table have two fields?
PermiteeID and ItemID? And does it contain a list of Permitees and the
items they are allowed to use? If so, and you know the ItemIDs of
interest (say these IDs are 1,2 and 3 per your initial post), and let's
also assume you are interested only in those permitted to own all three
of those items (excluding those that have been granted use for one or
two of them) then:

SELECT DISTINCT PermiteeID
FROM tblPermitItems
WHERE ItemID = 3
AND PermiteeID IN (SELECT PermiteeID FROM tblPermitItems WHERE ItemID=1)
AND PermiteeID IN (SELECT PermiteeID FROM tblPermitItems WHERE ItemID=2)
AND NOT PermiteeID IN (SELECT PermiteeID FROM tblPermitItems WHERE
ItemID NOT IN (1,2,3))

If you want all of the permitees restricted to any of the 1-3 items,
then

SELECT DISTINCT PermiteeID
FROM tblPermitItems
WHERE ItemID IN (1,2,3)
AND NOT PermiteeID IN (SELECT PermiteeID FROM tblPermitItems WHERE
ItemID NOT IN (1,2,3))

 

[StacyC]

OK, I tried that and did not get the results I needed. I'm probably not
articulating the goal/objective of my query precisely enough.
Thanks,
Stacy

I'm sorry, but I'm not looking over your shoulder at your puter screen


All I know so far is you have a table called tblPermitItems. What are
the relevant fields in this table? Does this table maintain the link
between permitees and items? Hmm ... does the table have two fields?
PermiteeID and ItemID? And does it contain a list of Permitees and the
items they are allowed to use? If so, and you know the ItemIDs of
interest (say these IDs are 1,2 and 3 per your initial post), and let's
also assume you are interested only in those permitted to own all three
of those items (excluding those that have been granted use for one or
two of them) then:

SELECT DISTINCT PermiteeID
FROM tblPermitItems
WHERE ItemID = 3
AND PermiteeID IN (SELECT PermiteeID FROM tblPermitItems WHERE ItemID=1)
AND PermiteeID IN (SELECT PermiteeID FROM tblPermitItems WHERE ItemID=2)
AND NOT PermiteeID IN (SELECT PermiteeID FROM tblPermitItems WHERE
ItemID NOT IN (1,2,3))

If you want all of the permitees restricted to any of the 1-3 items,
then

SELECT DISTINCT PermiteeID
FROM tblPermitItems
WHERE ItemID IN (1,2,3)
AND NOT PermiteeID IN (SELECT PermiteeID FROM tblPermitItems WHERE
ItemID NOT IN (1,2,3))

Beginnng from the bottom:
No, there is no field that identifies restricted permittes

[quoted text clipped - 20 lines]

 

[Bob Barrows]

It never hurts to show us a few rows of sample data in tabular form, and
follow that with the results you wish to obtain, again in tabular form.

OK, I tried that and did not get the results I needed. I'm probably
not articulating the goal/objective of my query precisely enough.
Thanks,
Stacy

I'm sorry, but I'm not looking over your shoulder at your puter
screen

All I know so far is you have a table called tblPermitItems. What are
the relevant fields in this table? Does this table maintain the link
between permitees and items? Hmm ... does the table have two fields?
PermiteeID and ItemID? And does it contain a list of Permitees and
the items they are allowed to use? If so, and you know the ItemIDs of
interest (say these IDs are 1,2 and 3 per your initial post), and
let's also assume you are interested only in those permitted to own
all three of those items (excluding those that have been granted use
for one or two of them) then:

SELECT DISTINCT PermiteeID
FROM tblPermitItems
WHERE ItemID = 3
AND PermiteeID IN (SELECT PermiteeID FROM tblPermitItems WHERE
ItemID=1) AND PermiteeID IN (SELECT PermiteeID FROM tblPermitItems
WHERE ItemID=2) AND NOT PermiteeID IN (SELECT PermiteeID FROM
tblPermitItems WHERE ItemID NOT IN (1,2,3))

If you want all of the permitees restricted to any of the 1-3 items,
then

SELECT DISTINCT PermiteeID
FROM tblPermitItems
WHERE ItemID IN (1,2,3)
AND NOT PermiteeID IN (SELECT PermiteeID FROM tblPermitItems WHERE
ItemID NOT IN (1,2,3))

Beginnng from the bottom:
No, there is no field that identifies restricted permittes

[quoted text clipped - 20 lines]

Permitee master table containing a field that identifies the
restricted Permitees?

 

[StacyC]

attached photos of relates and table views relevent ot this issue.
But I'n not sure how to tell ou to get there???
Stacy

It never hurts to show us a few rows of sample data in tabular form, and
follow that with the results you wish to obtain, again in tabular form.

OK, I tried that and did not get the results I needed. I'm probably
not articulating the goal/objective of my query precisely enough.

[quoted text clipped - 36 lines]

 

[Bob Barrows]

Sorry, but I really don't want to see your entire database structure

Boil it down to a concise summary that will not be too painful to type
into a newsgroup post, because that's the only way I will look at it.
Like this:

tblPemitItems
PermiteeID ItemID
1 1
1 2
1 3
2 1
2 2
2 3
3 1
4 2
5 1
5 4
5 28
6 5
7 1
7 2
7 3
7 4
7 5
8 1
8 2
8 3

desired results:
PermiteeID
1
2
3
4
8


A side benefit might be that it will allow you to see your solution
yourself.

attached photos of relates and table views relevent ot this issue.
But I'n not sure how to tell ou to get there???
Stacy

It never hurts to show us a few rows of sample data in tabular form,
and follow that with the results you wish to obtain, again in
tabular form.

OK, I tried that and did not get the results I needed. I'm probably
not articulating the goal/objective of my query precisely enough.

[quoted text clipped - 36 lines]

Permitee master table containing a field that identifies the
restricted Permitees?

 

[KenSheridan via AccessMonster.com]

On the basis of your HR analogy you seem to be saying you want to return
those permittees who hold 3 permits numbered 1 to 3, no more, no less, rather
than those who hold any 3 permits, but no more. If so try this:

SELECT *
FROM tblPermittees
WHERE
(SELECT COUNT(*)
FROM tblPermitDetails
WHERE tblPermitDetails.PermitteeID = tblPermittees.PermitteeID
AND PermitID IN (1,2,3)) = 3
AND NOT EXISTS
(SELECT *
FROM tblPermitDetails
WHERE tblPermitDetails.PermitteeID = tblPermittees.PermitteeID
AND PermitID >3);

Ken Sheridan
Stafford, England

 

[StacyC]

With 68 tables, 14 lookup tables: it was NOT the entire db structure. It was
2 tables and one view.
but thanks all the same.

Sorry, but I really don't want to see your entire database structure

Boil it down to a concise summary that will not be too painful to type
into a newsgroup post, because that's the only way I will look at it.
Like this:

tblPemitItems
PermiteeID ItemID
1 1
1 2
1 3
2 1
2 2
2 3
3 1
4 2
5 1
5 4
5 28
6 5
7 1
7 2
7 3
7 4
7 5
8 1
8 2
8 3

desired results:
PermiteeID
1
2
3
4
8

A side benefit might be that it will allow you to see your solution
yourself.

attached photos of relates and table views relevent ot this issue.
But I'n not sure how to tell ou to get there???

[quoted text clipped - 9 lines]

 

[StacyC]

Almost...but somewhat reversed: I want to see the permitees who have paid
for in combination of 3 certain facilites (a walkway, an underbrushing permit,
or a Rip-Rap voucher) and NOTHING ELSE. Other permitees can have those
things plus anything else on the list of 29 items....THEY are charged an
Overhead fee. The ones that have 3 or less of the ones in () are exempt from
paying an overhead fee. That is why i need to filter them out. {This policy
of changing who gets charged an OH fee just went into effect}. Does this
clarify ANYTHING?

On the basis of your HR analogy you seem to be saying you want to return
those permittees who hold 3 permits numbered 1 to 3, no more, no less, rather
than those who hold any 3 permits, but no more. If so try this:

SELECT *
FROM tblPermittees
WHERE
(SELECT COUNT(*)
FROM tblPermitDetails
WHERE tblPermitDetails.PermitteeID = tblPermittees.PermitteeID
AND PermitID IN (1,2,3)) = 3
AND NOT EXISTS
(SELECT *
FROM tblPermitDetails
WHERE tblPermitDetails.PermitteeID = tblPermittees.PermitteeID
AND PermitID >3);

Ken Sheridan
Stafford, England

Beginnng from the bottom:
No, there is no field that identifies restricted permittes

[quoted text clipped - 19 lines]

 

[KenSheridan via AccessMonster.com]

I'm still not really clear of the underlying criteria, I'm afraid. If what
you want are those permittees who hold any or all of the 3 relevant
facilities, but do not hold permits for any facilities other than these then
a small amendment to the first subquery should cater for that:

SELECT *
FROM tblPermittees
WHERE EXISTS
(SELECT *
FROM tblPermitDetails
WHERE tblPermitDetails.PermitteeID = tblPermittees.PermitteeID
AND PermitID IN (1,2,3))
AND NOT EXISTS
(SELECT *
FROM tblPermitDetails
WHERE tblPermitDetails.PermitteeID = tblPermittees.PermitteeID
AND PermitID >3);

This does of course assume that the PermitID values for the 3 relevant
facilities are 1, 2 and 3 and all others are greater than 3. If the values
for the 3 relevant ones do not in fact form a subsequence at the start of the
overall sequence you'd need to amend the value list for the IN operator in
the first subquery and use a slightly different operation in the second. Say
for instance the relevant 3 values are 2, 4 and 8:

SELECT *
FROM tblPermittees
WHERE EXISTS
(SELECT *
FROM tblPermitDetails
WHERE tblPermitDetails.PermitteeID = tblPermittees.PermitteeID
AND PermitID IN (2,4,8))
AND NOT EXISTS
(SELECT *
FROM tblPermitDetails
WHERE tblPermitDetails.PermitteeID = tblPermittees.PermitteeID
AND PermitID NOT IN(2,4,8));

Ken Sheridan
Stafford, England

Almost...but somewhat reversed: I want to see the permitees who have paid
for in combination of 3 certain facilites (a walkway, an underbrushing permit,
or a Rip-Rap voucher) and NOTHING ELSE. Other permitees can have those
things plus anything else on the list of 29 items....THEY are charged an
Overhead fee. The ones that have 3 or less of the ones in () are exempt from
paying an overhead fee. That is why i need to filter them out. {This policy
of changing who gets charged an OH fee just went into effect}. Does this
clarify ANYTHING?

On the basis of your HR analogy you seem to be saying you want to return
those permittees who hold 3 permits numbered 1 to 3, no more, no less, rather

[quoted text clipped - 21 lines]

 

[John W. Vinson]

Almost...but somewhat reversed: I want to see the permitees who have paid
for in combination of 3 certain facilites (a walkway, an underbrushing permit,
or a Rip-Rap voucher) and NOTHING ELSE. Other permitees can have those
things plus anything else on the list of 29 items....THEY are charged an
Overhead fee. The ones that have 3 or less of the ones in () are exempt from
paying an overhead fee. That is why i need to filter them out. {This policy
of changing who gets charged an OH fee just went into effect}. Does this
clarify ANYTHING?

These queries should do exactly what you describe if you replace Ken's 1, 2, 3
with the permit IDs for these specific facilities. Just replace the

=3

with

<= 3

to pick up the cases where the person has only one or two permits.

 

[StacyC via AccessMonster.com]

Ken,
Your solution is perfectly understandable (in logic) and see that is the
right direction; however, when i put this:
SELECT *
FROM tblPermitDetails
WHERE Exists
(SELECT *
FROM tblPermitDetails
WHERE ((tblPermitDetails.IDfacility) In (19,20,28))))
AND Exists
(SELECT *
FROM tblPermitDetails
WHERE ((tblPermitDetails.IDfacility) NOT In (19,20,28))));

into the SQL view, it provides me with 0 records. What am I missing
something? This is quite frustrating and seems to be unsolvable when i know
it IS solvable.

Stacy

I'm still not really clear of the underlying criteria, I'm afraid. If what
you want are those permittees who hold any or all of the 3 relevant
facilities, but do not hold permits for any facilities other than these then
a small amendment to the first subquery should cater for that:

SELECT *
FROM tblPermittees
WHERE EXISTS
(SELECT *
FROM tblPermitDetails
WHERE tblPermitDetails.PermitteeID = tblPermittees.PermitteeID
AND PermitID IN (1,2,3))
AND NOT EXISTS
(SELECT *
FROM tblPermitDetails
WHERE tblPermitDetails.PermitteeID = tblPermittees.PermitteeID
AND PermitID >3);

This does of course assume that the PermitID values for the 3 relevant
facilities are 1, 2 and 3 and all others are greater than 3. If the values
for the 3 relevant ones do not in fact form a subsequence at the start of the
overall sequence you'd need to amend the value list for the IN operator in
the first subquery and use a slightly different operation in the second. Say
for instance the relevant 3 values are 2, 4 and 8:

SELECT *
FROM tblPermittees
WHERE EXISTS
(SELECT *
FROM tblPermitDetails
WHERE tblPermitDetails.PermitteeID = tblPermittees.PermitteeID
AND PermitID IN (2,4,8))
AND NOT EXISTS
(SELECT *
FROM tblPermitDetails
WHERE tblPermitDetails.PermitteeID = tblPermittees.PermitteeID
AND PermitID NOT IN(2,4,8));

Ken Sheridan
Stafford, England

Almost...but somewhat reversed: I want to see the permitees who have paid
for in combination of 3 certain facilites (a walkway, an underbrushing permit,

[quoted text clipped - 10 lines]

 

[Bob Barrows]

You're not doing what he told you to do. Look again. You created two
mutually exclusive criteria and stated they both had to be true.
WHERE EXISTS (...) AND EXISTS (...)
It is impossible for both EXISTS to return True, hence zero records.

If you change the AND to OR so that one of them is allowed to be true,
the query would return ALL the records ... again, not what you want.
What Ken (and I in an earlier post) advised is to negate the second
criterion:

WHERE EXISTS (...) AND NOT EXISTS (...)

Ken,
Your solution is perfectly understandable (in logic) and see that is
the right direction; however, when i put this:
SELECT *
FROM tblPermitDetails
WHERE Exists
(SELECT *
FROM tblPermitDetails
WHERE ((tblPermitDetails.IDfacility) In (19,20,28))))
AND Exists
(SELECT *
FROM tblPermitDetails
WHERE ((tblPermitDetails.IDfacility) NOT In (19,20,28))));

into the SQL view, it provides me with 0 records. What am I missing
something? This is quite frustrating and seems to be unsolvable when
i know it IS solvable.

Stacy

I'm still not really clear of the underlying criteria, I'm afraid.
If what you want are those permittees who hold any or all of the 3
relevant facilities, but do not hold permits for any facilities
other than these then a small amendment to the first subquery should
cater for that:

SELECT *
FROM tblPermittees
WHERE EXISTS
(SELECT *
FROM tblPermitDetails
WHERE tblPermitDetails.PermitteeID = tblPermittees.PermitteeID
AND PermitID IN (1,2,3))
AND NOT EXISTS
(SELECT *
FROM tblPermitDetails
WHERE tblPermitDetails.PermitteeID = tblPermittees.PermitteeID
AND PermitID >3);

This does of course assume that the PermitID values for the 3
relevant facilities are 1, 2 and 3 and all others are greater than
3. If the values for the 3 relevant ones do not in fact form a
subsequence at the start of the overall sequence you'd need to amend
the value list for the IN operator in the first subquery and use a
slightly different operation in the second. Say for instance the
relevant 3 values are 2, 4 and 8:

SELECT *
FROM tblPermittees
WHERE EXISTS
(SELECT *
FROM tblPermitDetails
WHERE tblPermitDetails.PermitteeID = tblPermittees.PermitteeID
AND PermitID IN (2,4,8))
AND NOT EXISTS
(SELECT *
FROM tblPermitDetails
WHERE tblPermitDetails.PermitteeID = tblPermittees.PermitteeID
AND PermitID NOT IN(2,4,8));

Ken Sheridan
Stafford, England

Almost...but somewhat reversed: I want to see the permitees who
have paid for in combination of 3 certain facilites (a walkway, an
underbrushing permit,

[quoted text clipped - 10 lines]

Permitee master table containing a field that identifies the
restricted Permitees?