Grouping records by subsets SQL

Question

I have a database with PermitHolders (PermitNum = PK) and DetailedFacilities of each Permit Holder. In the tblPermitDetails table there are 2 columns

1. PermitNum (foreign Key)
2. FacilityID (integer Foreign Key Lookup to Facility table).

A permitee can have 1 - 29 items on their permit, e.i. Permit 50 can have a Boat Dock (FacID 4), a Paved walkway (FacID 17) a Retaining Wall (FacID 20) etc. I need an SQL filter/display whatever, ALL PERMIT #s that have ONLY FacIDs 19, 20, or 28, NOT ones that have those plus "x" others,....just that subset. I've worked on this for 4 days, would someone PLEASE help me? I HAVE posted to other BB but have not received any helpful suggestions.

As Oded suggested, here are more details. There is no PK for the tblPermitDetails table.

Let's say that we have Permitees 1 - 10; Permit 1 is John Doe, he has a Boat Dock (FacID 1), a Walkway (FacID 4), a buoy (FacID 7), and Underbrushing (FacID 19)...those are 3 records for Permit 1. Permit 2 is Sus Brown, she has ONLY underbrushing (FacID 19), Permit 3 is Steve Toni, he has a Boat Dock (FacID 1), a Walkway (FacID 4), a buoy (FacID 7), and a Retaining Wall (FacID 20). Permit 4 is Jill Jack, she has Underbrushing (FacID 19), and a Retaining Wall (FacID 20). I could go on but i hope you follow me. I want an SQL (for MS Access) that will show me ONLY Permits 2 & 4 because they have a combination of FacIDs 19 & 20 [either both, or one or the other], BUT NOT ANYTHING ELSE such as Permit 1 who has #19, but also has 4 & 7.

I hope that helps, please say so if not.

Oh yea, I DO know the difference between i.e. and e.g. since i'm in my 40's have written over 3000 pages of archaeological field reports and an MA thesis, but I'm really stressed out here from struggling with this SQL and could care less about consulting the Chicago Manual of Style before banging out a plea for help. SO, DON"T be coy about my compostion errors! Thank you!

Answer 1

Untested, but how about something like this?

SELECT DISTINCT p.PermitNum
           FROM tblPermitDetails p
          WHERE EXISTS
                (SELECT '+'
                   FROM tblFacility f
                  WHERE p.FacilityID = f.FacilityID
                    AND f.facilityID = 19 )
            AND EXISTS
                (SELECT '+'
                   FROM tblFacility f
                  WHERE p.FacilityID = f.FacilityID
                    AND f.facilityID = 20 )
            AND EXISTS
                (SELECT '+'
                   FROM tblFacility f
                  WHERE p.FacilityID = f.FacilityID
                    AND f.facilityID = 28 )
            AND NOT EXISTS
                (SELECT '+'
                   FROM tblFacility f
                  WHERE p.FacilityID = f.FacilityID
                    AND f.facilityID NOT IN (19,20,28) )

Answer 2

Quick way might be to only look at the ones with exactly three matches (with an inner query), and then among those only include the ones that have 19, 20, and 28.

Of course, that is sort of a brute force method, and not very elegant. But it has the small benefit of being understandable. None of the approaches I can think of will be easy to customize to various other sets of values.

Answer 3

SELECT PermitNum
FROM tblPermitDetails
WHERE FacilityID IN (19, 20, 28)
GROUP BY PermitNum
HAVING COUNT(PermitNum)=3

Answer 4

Ok, it seems i didn't understand the problem at first. So, again:

I will recreate the example by Stacy here:

DECLARE @PermitHolders TABLE 
(PermitNum INT NOT NULL,
PermitHolder VARCHAR(20))

DECLARE @tblPermitDetails TABLE
(PermitNum INT,
FacilityID INT)

INSERT INTO @PermitHolders VALUES (1, 'John Doe')
INSERT INTO @PermitHolders VALUES (2, 'Sus Brown')
INSERT INTO @PermitHolders VALUES (3, 'Steve Toni')
INSERT INTO @PermitHolders VALUES (4, 'Jill Jack')

INSERT INTO @tblPermitDetails VALUES (1, 1)
INSERT INTO @tblPermitDetails VALUES (1, 4)
INSERT INTO @tblPermitDetails VALUES (1, 7)
INSERT INTO @tblPermitDetails VALUES (1, 19)
INSERT INTO @tblPermitDetails VALUES (2, 19)
INSERT INTO @tblPermitDetails VALUES (3, 1)
INSERT INTO @tblPermitDetails VALUES (3, 4)
INSERT INTO @tblPermitDetails VALUES (3, 7)
INSERT INTO @tblPermitDetails VALUES (3, 20)
INSERT INTO @tblPermitDetails VALUES (4, 19)
INSERT INTO @tblPermitDetails VALUES (4, 20)

And this is the solution:

SELECT * FROM @PermitHolders 
WHERE (PermitNum IN (SELECT PermitNum FROM @tblPermitDetails WHERE FacilityID IN (19, 20, 28)))
AND (PermitNum NOT IN (SELECT PermitNum FROM @tblPermitDetails WHERE FacilityID NOT IN (19, 20, 28)))

I have one observation on the side: You didn't mention any PK for tblPermitDetails. If non exists, this may not be good for performance. I recommend that you create a PK using both PermitNum and FacilityID (composite key) because this will serve as both your PK and a useful index for the expected queries.

Answer 5

I wasn't sure if you wanted ALL of 19,20,28 or ANY of 19,20,28... also, this is untested, but if you want the any of solution it should be fairly close

Select
    allowed.PermitNum
from
    DetailedFacilties allowed
    join DetailedFacilities disallowed on allowed.PermitNum != disallowed.PermitNum
where
    allowed.FacilityID in (19, 20, 28)
    and disallowed.FacilityID not in (19, 20, 28)

Answer 6

SELECT DISTINCT PermitNum FROM tblPermitDetails t1
 WHERE FacilityID IN (19, 20, 28)
   AND NOT EXISTS (SELECT 1 FROM tblPermitDetails t2
                    WHERE t2.PermitNum = t1.PermitNum
                      AND FacilityId NOT IN (19, 20, 28));

Or, in prose, get the list of PermitNums that have any of the requested permit numbers as long as no row exists for that PermitNum that isn't in the requested list.

A more optimized version of the same query would be the following:

SELECT PermitNum FROM (SELECT DISTINCT PermitNum FROM tblPermitDetails
                        WHERE FacilityID IN (19, 20, 28)) AS t1
 WHERE NOT EXISTS (SELECT 1 FROM tblPermitDetails t2
                    WHERE t2.PermitNum = t1.PermitNum
                      AND FacilityID NOT IN (19, 20, 28));

It's a little harder to read, but it will involve fewer "NOT EXISTS" subqueries by doing the "DISTINCT" part first.

Update:

David-W-Fenton mentions that NOT EXISTS should be avoided for optimization reasons. For a small table, this probably won't matter much, but you could also do the query using COUNT(*) if you needed to avoid NOT EXISTS:

SELECT DISTINCT PermitNum FROM tblPermitDetails t1
 WHERE (SELECT COUNT(*) FROM tblPermitDetails t2
         WHERE t1.PermitNum = t2.PermitNum
           AND FacilityID IN (19, 20, 28))
       =
       (SELECT COUNT(*) FROM tblPermitDetails t3
         WHERE t1.PermitNum = t3.PermitNum)

Answer 7

What about (untested)

select permitnum 
from tblPermitDetails t1
left outer join
(Select distinct permitnum from tblPermitDetails where facilityId not in (19, 20, or 28)) t2
on t1.permitnum=t2.permitnum
where t2.permitnum is null

i.e. we find all the permits that cannot match your criteria (they have at least one detail outside those you list), then we find all the permits that are left, via a left join and where criteria.

with indexes set up properly, this should be pretty quick.