Get rows where the entire group does NOT have a value-CodePudding

Given a table with grouped data (below), what is a query that selects only values where the entire group does not have a specified value?

Here is my data:

id  firstid lastid  descrip
1   1       2       Y
2   2       3       Y
3   2       4       N
4   2       5       Y
5   3       6       Y
5   3       7       Y
5   4       8       N
5   4       9       N
6   4       10      Y
7   5       11      N
8   5       12      N
9   6       13      Y
10  6       14      Y
11  7       15      Y
12  7       16      N

In this case, I want to get the FirstId of the data where none of the rows with that particular FirstId have a Descrip of Y. E.g., the FirstId 2 has one "Y" in it, and FirstId 7 also has a "Y" in it. So the result should be "5" for this example case.

I tried,

select FirstId, descrip from sampletable
where descrip <> 'Y'
group by FirstId, descrip

CodePudding user response：

set nocount on
declare @sampletable table
(
  id      int
 ,firstid int
 ,lastid  int
 ,descrip nvarchar(1)
)
insert into @sampletable values(1 ,1 ,2 ,'Y')
insert into @sampletable values(2 ,2 ,3 ,'Y')
insert into @sampletable values(3 ,2 ,4 ,'N')
insert into @sampletable values(4 ,2 ,5 ,'Y')
insert into @sampletable values(5 ,3 ,6 ,'Y')
insert into @sampletable values(5 ,3 ,7 ,'Y')
insert into @sampletable values(5 ,4 ,8 ,'N')
insert into @sampletable values(5 ,4 ,9 ,'N')
insert into @sampletable values(6 ,4 ,10,'Y')
insert into @sampletable values(7 ,5 ,11,'N')
insert into @sampletable values(8 ,5 ,12,'N')
insert into @sampletable values(9 ,6 ,13,'Y')
insert into @sampletable values(10,6 ,14,'Y')
insert into @sampletable values(11,7 ,15,'Y')
insert into @sampletable values(12,7 ,16,'N')
--Show FirstIDs where the # of "N" entries is the same as the total # of entries
SELECT ST.FIRSTID
FROM @SAMPLETABLE ST
WHERE ST.DESCRIP = 'N'
GROUP BY ST.FIRSTID, ST.DESCRIP
HAVING COUNT(*) = (
  SELECT COUNT(*) FROM @SAMPLETABLE ST2 WHERE ST2.FIRSTID = ST.FIRSTID
  )

CodePudding user response：

Maybe a bit complicated, but I would try something like this:

select
  a.firstid
from
  sampletable a
  left outer join (
  select
    id
  from
    sampletable
  where
    descrip = 'Y'
  group by 1
  ) b ON a.id = b.id
where
  b.id is null
group by 1