SQL subsets
in [Queries]
|
From: Stacy in Savannah Stacy in on 14 May 2010 09:10 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
From: Bob Barrows on 14 May 2010 09:25 Stacy in Savannah wrote: > 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? -- HTH, Bob Barrows
From: StacyC on 14 May 2010 10:07 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. Bob Barrows wrote: > >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? >
From: KARL DEWEY on 14 May 2010 10:39 Try this using your table and field names -- SELECT Permitee FROM YourTable GROUP BY YourTable.Permitee HAVING (((Count(YourTable.[Items]))<=3)); -- Build a little, test a little. "Stacy in Savannah" wrote: > 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
From: Bob Barrows on 14 May 2010 10:55
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 wrote: > 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. > > Bob Barrows wrote: >> >> 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? -- HTH, Bob Barrows |