How do I find groups of rows where all rows in each group have a specific column value

Question

Sample data:

ID1   ID2   Num  Type
---------------------
1     1     1    'A'
1     1     2    'A'
1     2     3    'A'
1     2     4    'A'
2     1     1    'A'
2     2     1    'B'
3     1     1    'A'
3     2     1    'A'

Desired result:

ID1   ID2
---------
1     1
1     2
3     1
3     2

Notice that I'm grouping by ID1 and ID2, but not Num, and that I'm looking specifically for groups where Type = 'A'. I know it's doable through a join two queries on the same table: one query to find all groups that have a distinct Type, and another query to filter rows with Type = 'A'. But I was wondering if this can be done in a more efficient way.

I'm using SQL Server 2008, and my current query is:

SELECT ID1, ID2
FROM (
    SELECT ID1, ID2
    FROM T
    GROUP BY ID1, ID2
    HAVING COUNT( DISTINCT Type ) = 1
) AS SingleType
INNER JOIN (
    SELECT ID1, ID2
    FROM T
    WHERE Type = 'A'
    GROUP BY ID1, ID2
) AS TypeA ON
    TypeA.ID1 = SingleType.ID1 AND
    TypeA.ID2 = SingleType.ID2

EDIT: Updated sample data and query to indicate that I'm grouping on two columns, not just one.

What's your current query look like? – N West Feb 03 '12 at 16:49 — N West, Feb 03 '12 at 16:49

Bassam Mehanni · Accepted Answer · 2012-02-03T23:43:18.700

5

SELECT ID1, ID2
FROM MyTable
GROUP BY ID1, ID2
HAVING COUNT(Type) = SUM(CASE WHEN Type = 'A' THEN 1 ELSE 0 END)

edited Feb 03 '12 at 23:43

answered Feb 03 '12 at 16:52

Bassam Mehanni

14,796
2
33
41

Thanks Bassam, but I'm having trouble with running this query since in my real data ID1 is not just a single column, so it's complaining that the COUNT function requires 1 argument. I'll update my sample data. – khaledh Feb 03 '12 at 17:00
@JNK I am sure you would have caught it on your own on a double take :D – Bassam Mehanni Feb 03 '12 at 17:00
@BassamMehanni - probably but this answer is the right one, I think. – JNK Feb 03 '12 at 17:03
@khaledh try replacing Count(ID1) with COUNT(Type) it should be effectively the same because it should be equivalent to the number of rows – Bassam Mehanni Feb 03 '12 at 17:04
Minor detail: To match the question as currently written, I guess both instances of `ID1` should be replaced with `ID1, ID2`. This confused me for a moment, so I thought I'd mention it. – Ilmari Karonen Feb 03 '12 at 23:07

score 1 · Answer 2 · edited Jan 18 '21 at 12:32

There are two alternatives that don't require the aggregation (but do require distinct)

ANTI-JOIN

SELECT DISTINCT t1.ID1, t1.ID2 
FROM
    table  t1
    LEFT JOIN table t2
    ON t1.ID1 = t2.ID1
        and t1.Type <> t2.Type
WHERE
    t1.Type = 'A'
    AND 
    t2.ID1 IS NULL

See it working at this data.se query Sample for 9132209 (Anti-Join)

NOT EXISTS

SELECT DISTINCT t1.ID1, t1.ID2 
FROM
    table  t1
WHERE
    t1.Type = 'A'
AND
   NOT EXISTS 
      (SELECT 1 
       FROM table t2 
       WHERE t1.ID1 = t2.ID1 AND Type <> 'A')

See it working at this data.se query Sample for 9132209 Not Exists

How do I find groups of rows where all rows in each group have a specific column value

2 Answers2

Linked