4

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

New contributor
Magrathea is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
1
  • What is the desired output?
    – Rick James
    Jun 22 at 5:38

4 Answers 4

5

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

0
3

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

0
1

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

0
0

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

0

Your Answer

Magrathea is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.