5
votes
Accepted
Select only rows where all values in a column match a value for an id
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 ...
3
votes
Select only rows where all values in a column match a value for an id
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
- 5,928
1
vote
Select only rows where all values in a column match a value for an id
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
...
- 1,186
Only top scored, non community-wiki answers of a minimum length are eligible