Select column value if has same value in the entire column

user_11171019 0
Viewed 73
id tag title status
1 tag1 title complete
2 tag2 title pending
3 tag3 title complete
4 tag4 title pending
5 tag1 title complete
6 tag2 title complete
7 tag4 title complete
8 tag2 title pending

In my input table, three main fields are recorded:

  • "tag", which is not unique
  • "title", which is unique
  • "status", which is not unique and whose values are either 'pending' or complete

I'd like to check which tags assume the value 'complete' on the "status" field for all the rows associated to that tag. If a tag has at least a 'pending' row, that tag should not be included in the output result set.

Can you help me with this task?

mysql sql
4 Answers

Better solution would be to split your data storage in two tables - tags and tasks, but even as is you can get the tag of a fully complete task like this:

SELECT d.tag FROM mytable d
INNER JOIN (SELECT b.tag FROM (SELECT a.tag, COUNT(a.status) cnt FROM (SELECT tag, status FROM mytable GROUP BY tag, status) a GROUP BY a.tag) b WHERE cnt = 1) as c
ON d.tag = c.tag
WHERE d.status = 'complete'

Be aware that increasing of dataset size will significally impact on script performance

user_140272300

You can remove all occurrences of tags with at least one pending operation from the resultset:

SELECT DISTINCT tag 
FROM   tab
WHERE  tag NOT IN (SELECT tag
                   FROM   tab 
                   WHERE  status = 'pending')

Check the demo here.

user_124928900

Just wanted to add a small "cheat" solution to all the already great answers above,
assuming you only have completed/pending statuses and that won't change

To get all the ONLY completed tags :

SELECT tag FROM tableName
GROUP BY tag
HAVING MAX(status) = 'completed'

To get all the ONLY pending tags:

SELECT tag FROM tableName
GROUP BY tag
HAVING MIN(status) = 'pending'

Please note that in the first one, I used MAX and in the second I used MIN
This little cheat is working since completed is "smaller" than pending in alphabetical order, so if that value is available in one of the tag records, it will always be the value returned by the MIN function, and if pending is available it will be returned by the MAX function.

Therefore, in the first example, if the MAX is "completed"? that means there was no records with "pending" status

And in the second example, if the MIN is "pending", that means there were no records with "completed" status

user_81485590

you can use this

SELECT * FROM tableName GROUP BY status;
user_166902810
Related