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 ...
Community wiki
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
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
...
Only top scored, non community-wiki answers of a minimum length are eligible
Related Tags
select × 1016mysql × 428
postgresql × 159
sql-server × 133
join × 103
oracle × 89
performance × 72
query × 57
group-by × 44
insert × 41
count × 40
query-performance × 36
update × 33
subquery × 32
index × 30
sqlite × 28
optimization × 26
mariadb × 25
distinct × 23
order-by × 22
innodb × 21
mysql-5.5 × 20
database-design × 19
aggregate × 17
where × 17