Select only rows where all values in a column match a value for an id

Question

Given the table

seq_id	seq_status
1	A
1	B
2	A
2	A
3	A
4	B
5	C

I want to select the distinct seq_id's where the seq_status is A (2 and 3) and not return if one of the values is different

so far I have something that looks like this

SELECT distinct(s.seq_id) FROM sequence s
having s.seq_id IN (
    select z.seq_id
    from sequence z
    group by z.seq_id, z.seq_status
    having z.seq_status = "A" )

But that returns any seq_id with seq_status A and I'm looking for only seq_id where the seq_status all match the required value

Answer 1

Yet another alternative:

# All non-NULL seq_status values per seq_id contain 'A'
SELECT s.seq_id
FROM sequence AS s
GROUP BY s.seq_id
HAVING SUM(s.seq_status = 'A') = COUNT(*);

That works because comparison operations result in a value of 1 (TRUE), 0 (FALSE), or NULL.

There are a large number of alternatives, including:

HAVING COUNT(DISTINCT seq.status) = 1 AND MIN(seq_status)='A'
HAVING SUM(s.seq_status <> 'A' OR seq.status IS NULL) = 0

Not to mention queries written using EXISTS or = ALL.

---

Or if you want to disregard NULLs:

SELECT s.seq_id
FROM sequence AS s
GROUP BY s.seq_id
HAVING MIN(s.seq_status) = 'A'
   AND MAX(s.seq_status) = 'A';

db<>fiddle demo

Answer 2

Seems like you just need a conditional count in a HAVING

SELECT s.seq_id
FROM sequence s
GROUP BY
  s.seq_id
HAVING COUNT(CASE WHEN s.seq_status <> 'A' THEN 1 END) = 0;

db<>fiddle

Answer 3

Another option, slightly different from @Charlieface is using HAVING with GROUP_CONCAT. It will return only the seq_id where distinct seq_status is equal to A.

Try:

SELECT s.seq_id
FROM sequence s
GROUP BY  s.seq_id
HAVING GROUP_CONCAT(DISTINCT seq_status) ='A';

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=badd25464f6cc03b952ec6d35a9446eb

Answer 4

For MySQL 8.* this can be made with CTEs:

WITH cte AS (
  SELECT seq_id, seq_status
    FROM sequence
   GROUP BY seq_id, seq_status
  )
  SELECT w.seq_id
    FROM      cte AS w
    LEFT JOIN cte AS z  ON z.seq_id = w.seq_id
                       AND z.seq_status != w.seq_status
   WHERE w.seq_status = "A"
     AND z.seq_id IS NULL
;

This query will return all sequences having their statuses "A" and only "A".