I have a column with can be any of three possible elements, call them x,y,z. The column is not allowed to be only z.

I can imagine this in set theory where S = {x,y,z}, and I require all subsets of S except the set {z}.

How would I write a SQL statement that let's the column be any subset of x,y,z except z alone?

For example:

ID	Value
1	x
1	y
1	z
2	x
2	z
3	z
3	z

Desired output:

ID	Value
1	x
1	y
1	z
2	x
2	z

CodePudding user response：

You may try with count window function as the following:

Select ID, Value
From
(
  Select ID, Value,
    Count(Case When Value = 'z' Then 1 End) Over (Partition By ID) As z_cnt, 
    Count(*) Over (Partition By ID) As all_cnt
  From table_name
) T
Where z_cnt <> all_cnt

See demo.

CodePudding user response：

You can use an exists semi-join:

    select *
    from t
    where not exists (
        select *
        from t t2
        where t2.Id = t.Id and t.Value = 'Z'
        group by id 
        having Count(distinct value) = 1
    );

CodePudding user response：

You can use EXCEPT with your conditions

SELECT [ID], [Value] FROM tab
EXCEPT
SELECT [ID], [Value] FROm tab t1 WHERE  [Value] = 'z' AND (SELECT COUNT(*) FROm tab WHERE [ID] = t1.[ID]) = 1

ID	Value
1	x
1	y
1	z
2	x
2	z

fiddle

If the data are not unique you can use NOT IN instead of

CREATE TABLE tab
    ([ID] int, [Value] varchar(1))
;
    
INSERT INTO tab
    ([ID], [Value])
VALUES
    (1, 'x'),
    (1, 'y'),
    (1, 'z'),
    (2, 'x'),
    (2, 'z'),
    (3, 'z'),
    (3, 'z'),
    (3, 'z'),
    (4, 'x'),
    (4, 'x'),
    (4, 'x'),
    (5, 'y'),
    (5, 'y'),
    (5, 'y')
;

14 rows affected

SELECT [ID], [Value] FROM tab
WHERE [ID] NOt IN (
SELECT [ID] FROm tab t1 WHERE  [Value] = 'z' 
  AND (SELECT COUNT(DISTINCT [Value]) FROm tab WHERE [ID] = t1.[ID]) = 1)

ID	Value
1	x
1	y
1	z
2	x
2	z
4	x
4	x
4	x
5	y
5	y
5	y

fiddle