Select column value if has same value in the entire column
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
you can use this
SELECT * FROM tableName GROUP BY status;
SELECT GROUP_CONCAT(tag) FROM tableName WHERE status = 'completed' or status = 'pending' GROUP BY status;
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.
Better solution would be to split your data storage in two tables - tags and tasks, but even as is you can get the
tagof 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
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
MAXand in the second I used
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
MINfunction, and if pending is available it will be returned by the
Therefore, in the first example, if the
MAXis "completed"? that means there was no records with "pending" status
And in the second example, if the
MINis "pending", that means there were no records with "completed" status