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;