SQL subsets
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?
KenSheridan wrote:
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]
Permitee master table containing a field that identifies the restricted
Permitees?
|