CODEWITHSUNDEEP
sqlsql-server
w0051977
w0051977

Reputation: 15807

Filtering a group of records

Please see the SQL structure below:

CREATE table TestTable (id int not null identity, [type] char(1), groupid int)

INSERT INTO TestTable ([type]) values ('a',1)
INSERT INTO TestTable ([type]) values ('a',1)
INSERT INTO TestTable ([type]) values ('b',1)
INSERT INTO TestTable ([type]) values ('b',1)
INSERT INTO TestTable ([type]) values ('a',2)
INSERT INTO TestTable ([type]) values ('a',2)

The first four records are part of group 1 and the fifth and sixth records are part of group 2.

If there is at least one b in the group then I want the query to only return b's for that group. If there are no b's then the query should return all records for that group.

Upvotes: 2

Views: 38

Answers (1)

Hogan
Hogan

Reputation: 70523

Here you go

SELECT *
FROM testtable
LEFT JOIN (SELECT distinct groupid FROM TestTable WHERE type = 'b'
) blist ON blist.groupid = testtable.groupid
WHERE (blist.groupid = testtable.groupid and type = 'b') OR
      (blist.groupid is null)

How it works

  • join to a list of items that contain b.

  • Then in where statement... if we exist in that list just take type b. Otherwise take everything.

As an after-note you could be cute with the where clause like this

WHERE ISNULL(blist.groupid,testtable.groupid) = testtable.groupid

I think this is less clear -- but is often how advanced users will do it.

Upvotes: 3

Related Questions