# 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';
``````

### 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 🙂