Select column value if has same value in the entire column-CodePudding

i have a table where i have stored 3 columns - id, tag, title, status. tag is not unique, title is unique and status is either complete or pending.

The mysql table looks like that

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

Can i check which tags are all complete and are all pending. So i want to know which are tags are all completed then the query should return the value of tag1 according to the above table

CodePudding user response：

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')

SQL Fiddle here: https://www.db-fiddle.com/f/apRvzhs45hDXy59WZAXEbR/0.

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

Try this

  SELECT GROUP_CONCAT(tag) FROM tableName WHERE status = 'completed' or status = 'pending' GROUP BY status;

CodePudding user response：

you can use this

SELECT * FROM tableName GROUP BY status;