sql: trouble with where clause-CodePudding

I have a table that looks like this:

Column A	Column B
P33	x
P33	y
P22	x
N44	y
D99	null

I need to select all the values in Column A that do not have 'x' and 'y' in Column B, and the ones that have null in the second table. So the result should look like this:

Result
P22
N44
D99

CodePudding user response：

Here's an answer that account for duplicities in the count and works with null.

select   [Column A]
        
from     t

group by [Column A]

having   count(case [Column B] when 'x' then 1 end) = 0 
      or count(case [Column B] when 'y' then 1 end) = 0

Column A
D99
N44
P22

Fiddle

CodePudding user response：

This is mySQL ver5.6.

SELECT ColumnA
FROM (
  SELECT 
   ColumnA,
   GROUP_CONCAT(ColumnB ORDER BY ColumnB) as ColumnXY
FROM 
   table1
GROUP BY
   ColumnA
  ) table2
WHERE ColumnXY <> 'x,y' OR ColumnXY is NULL;

CodePudding user response：

use conditional aggregation

    select t.columna from table1 t
    where  columnb in ('x','y')
    having count(distinct Columnb)<2
     group by t.columna 
   union all
   select t.columna from table1 t
   where columnb is null and columnb not in ('x','y')

CodePudding user response：

You can use two anti-joins. For example:

select a.column_a
from t a
left join t x on x.column_a = a.column_a and x.column_b = 'x'
left join t y on y.column_a = a.column_a and y.column_b = 'y'
where x.column_a is null or y.column_a is null