CODEWITHSUNDEEP
sqlselect
Cherry
Cherry

Reputation: 23

Grouping of column

Group   Error  
1           a  
1           b  
1           c    
2           a  
2           b  
3           a

I want write an SQL query to get that record only which has either only a or b as an error or both a and b
Output should be Group 2, 3 as Group 2 contains both a and b , Group 3 contains only a.
Any Group that contains Error apart from a,b should not be returned.

Upvotes: 1

Views: 59

Answers (2)

Lee Mac
Lee Mac

Reputation: 16015

Unsure of the DBMS and so the following may not be applicable, but to offer another option using a correlated subquery:

SELECT DISTINCT a.Group
FROM Table1 a
WHERE NOT EXISTS (SELECT 1 FROM Table1 b WHERE a.Group = b.Group AND b.Error NOT IN ('a','b'))

Or using a LEFT JOIN on a query of Groups containing at least one Error code not equal to a or b:

SELECT DISTINCT a.Group
FROM 
    Table1 a LEFT JOIN
    (
        SELECT DISTINCT t.Group
        FROM Table1 t
        WHERE t.Error <> 'a' AND t.Error <> 'b'
    ) b
    ON a.Group = b.Group
WHERE b.Group IS NULL

And one more very specific to your example, but just for fun -

SELECT t.Group
FROM Table1 t
GROUP BY t.Group
HAVING MIN(t.Error) >= 'a' AND MAX(t.Error) <= 'b'

Replace Table1 with the name of your table in all of the above.

Upvotes: 1

Mureinik
Mureinik

Reputation: 311393

I'd group and use a condition on a count of errors that aren't a or b:

SELECT   [group] -- Assuming MS SQL Syntax, like Ross Presser did in his answer
FROM     mytable
GROUP BY [group]
HAVING   COUNT(CASE WHEN [error] NOT IN ('a', 'b') THEN 1 END) = 0

Upvotes: 2

Related Questions