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

All we need is an easy explanation of the problem, so here it is.

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

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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

Method 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

Method 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

Method 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".

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply