SQL Server Get Distinct Values With Consistent Value

cuxqih21 于 2024-01-05 发布在其他

关注(0)|答案(2)|浏览(167)

I have a table with some IDs and a flag of whether or not the person should be deleted. There are 2 sets of IDs in the table, 1 primary ID, and then a secondary ID as well. A single primary ID can be in the table multiple times if it has multiple secondary IDs.

Take this set of data for example:

primary_id       secondary_id      delete_flag
   1                 ajdkl              1
   1                 jnami              0
   2                 janda              0
   3                 wwqia              1
   4                 lamse              1
   4                 pppqw              1
   4                 aneqw              0
   5                 gaabs              1
   5                 iikam              1

in that example I would be looking to have 3 and 5 returned since they are the only 2 where all instances of their primary ID have a deleted_flag = 1.

What I am running in to in this query:

select primary_id from table where delete_flag = 1 group by primary_id

is that it is returning all instances where there at least a single 1 present, I want only the ones where every instance has a 1 present.

sql-server

来源：https://stackoverflow.com/questions/77571421/get-distinct-values-with-consistent-value

2条答案

按热度按时间

ax6ht2ek1#

You need to use GROUP BY and HAVING clauses, where the primary_ids whose total number of records is equal to the total number of deleted flag records should be selected.

select primary_id 
from mytable 
group by primary_id 
having count(delete_flag) = count(case when delete_flag = 1 then 1 end)

Another option is to use MIN() and MAX() , where the deleted_flag's min and max should both equal 1.

select primary_id 
from mytable 
group by primary_id 
having min(delete_flag) = 1 and max(delete_flag) = 1

Demo here

赞(0）回复(0）举报 2024-01-05

yb3bgrhw2#

select * from myTable t1 where not exists 
(select * from myTable t2 
 where t1.primary_id=t2.primary_id and t2.Delete_flag = 0);

EDIT: If you meant to get just primary_id distinct values:

select distinct primary_id from myTable t1 where not exists 
(select * from myTable t2 
 where t1.primary_id=t2.primary_id and t2.Delete_flag = 0);

DBFiddle demo

赞(0）回复(0）举报 2024-01-05

我来回答

SQL Server Get Distinct Values With Consistent Value

2条答案

相关问题

热门标签

最新问答