Grouping records by subsets of SQL

Question

I have a database with PermitHolders (PermitNum = PK) and DetailedFacilities of each permission holder. There are 2 columns in the tblPermitDetails table

• PermitNum (foreign key)
• FacilityID (integer foreign key to search in the Facility table).

The authorized may have 1 - 29 items at their permission, ei Permission 50 may have a docking station (FacID 4), a paved track (FacID 17), a retaining wall (FacID 20), etc. I need an SQL filter / display, everything, ALL PERMIT #, which have ONLY FILES 19, 20 or 28, not those who have plus "x" others, ... only this is a subset. I have been working on this for 4 days, anyone PLEASE help me? I sent to another BB, but did not get any useful tips.

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

Let's say that we have Permitees 1-10; Resolution 1 - John Doe, he has a docking station for boats (FacID 1), Walkway (FacID 4), buoy (FacID 7) and Underbrushing (FacID 19) ... these are 3 entries for permission 1. Resolution 2 Sus Brown , it has ONLY undergrowth (FacID 19), Permit 3 is Steve Tony, it has a boat dock (FacID 1), Walkway (FacID 4), buoy (FacID 7) and Retaining wall (FacID 20), Resolution 4 - Jill Jack, she has Undergrowth (FacID 19) and Retaining Wall (FacID 20). I could go on, but I hope you follow me. I want SQL (for MS Access) to show me ONLY permissions 2 and 4, because they have a combination of identifiers 19 and 20 (both or one or the other), BUT NOT ANYTHING, for example, permission 1, which has # 19, but also has 4 and 7.

, , , , .

, , .. 40, 3000 , SQL , , . , " ! !

Answer 1

, - ?

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

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

Answer 3

, 19,20,28 19,20,28... , , , -

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 4

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));

, , PermitNums, - , PermitNum , .

:

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));

, " ", "DISTINCT".

Update:

-- , . , , , , COUNT (*), 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 5

()

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

. , ( ), , , .

, .

Answer 6

, , ( ), , , 19, 20 28.

, , . , . , , .

Answer 7

, , . , :

Stacy:

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)

:

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 to the side: You did not specify any PKs for tblPermitDetails. If not, this may not be very useful for performance. I recommend that you create a PK using both PermitNum and FacilityID (composite key), because it will serve as both your PC and a useful index for expected queries.