Select records if all in group have the same value

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select records if all in group have the same value

Author

Topic

Hammerklavier
Starting Member

26 Posts

Posted - 2015-02-11 : 13:01:52

Hi!

Here is my table data:


CREATE TABLE 
	#TestTable (
								Pk INT,
								GroupID INT,
								Enabled BIT
							)

INSERT INTO #TestTable (Pk, GroupID, Enabled) VALUES (1,1,1)
INSERT INTO #TestTable (Pk, GroupID, Enabled) VALUES (3,2,1)
INSERT INTO #TestTable (Pk, GroupID, Enabled) VALUES (4,2,1)
INSERT INTO #TestTable (Pk, GroupID, Enabled) VALUES (5,2,1)
INSERT INTO #TestTable (Pk, GroupID, Enabled) VALUES (6,2,1)
INSERT INTO #TestTable (Pk, GroupID, Enabled) VALUES (7,2,0)
INSERT INTO #TestTable (Pk, GroupID, Enabled) VALUES (8,2,1)
INSERT INTO #TestTable (Pk, GroupID, Enabled) VALUES (9,2,1)
INSERT INTO #TestTable (Pk, GroupID, Enabled) VALUES (10,3,1)
INSERT INTO #TestTable (Pk, GroupID, Enabled) VALUES (11,3,1)
INSERT INTO #TestTable (Pk, GroupID, Enabled) VALUES (12,3,1)

SELECT * FROM #TestTable

DROP TABLE #TestTable

I need to write a select query that will retrieve any GroupID in which every record has an Enabled value of 1.

In the example I've provided, only GroupID 1 and 3 will be returned since GroupID 2 has a record with an Enabled value of 0.

What would be the most efficient way to write such a query?

I appreciate your help!

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-02-11 : 13:14:27

One of these should do it:

select *
  from #TestTable as t1
 where not exists (select *
                     from #TestTable as t2
                    where t2.GroupID=t1.GroupID
                      and t2.Enables<>1
                  )

select t1.*
  from #TestTable as t1
       inner join (select GroupID
                         ,sum(case when Enabled=1 then 0 else 1 end) as not_enabled
                     from #TestTable
                    group by GroupId
                  ) as t2
               on t2.GroupID=t1.GroupID

Hammerklavier
Starting Member

26 Posts

Posted - 2015-02-12 : 12:08:18

I will give this a try. Thank you bitsmed!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources