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

5 answers

  • answered 2022-05-04 10:05 ILLUMINAG gaming

    you can use this

    SELECT * FROM tableName GROUP BY status;
    

  • answered 2022-05-04 10:23 Abhishek Mishra

    Try this

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

  • answered 2022-05-04 10:37 lemon

    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.

  • answered 2022-05-04 10:46 meridbt

    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

  • answered 2022-05-04 11:13 Saar Levy

    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

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum