Clever Geek Handbook

MySQL - query to get a specific result

I started learning MySQL and I got stuck on the case.

I have the following table:

id | value
1  | abc
1  | def
2  | 
2  | 
3  | pqr
3  | 
4  |
4  | xyz
5  |

Note that empty values ​​next to a numeric int means empty strings.

Assertion about the problem : I need to get those IDs which, if formed into a group, will only have empty strings. For example, there will be identifiers: 2, 5

Explanation: id = 2 appears twice with blank lines and is therefore included. id = 5 appears once and has one blank line, so it is included. id = 3 is not included because one of its instances has a non-empty value, that is, "pqr"

I am stuck with a request:

SELECT * FROM t1 GROUP BY id;

But this gives the wrong result.

Could you help me? What should be the query to get ids = 2, 5. I'm sorry about formatting the table.

+3


source to share


4 answers 


SELECT DISTINCT t1.id 
FROM t1
LEFT JOIN t1 t1d ON t1d.id = t1.id AND t1d.value <> ''
WHERE t1d.id IS NULL


without GROUP BY

and HAVING

= one happy database!

0


source


You can achieve the expected result with conditional counting versus counting all rows within a group:

 select id from t1
 group by id
 having count(*)=count(if(`value`='',1,null))


count(*)

returns the number of records with the matching ID. count(if(

value ='',1,null))

returns the number of such records where the field value

is an empty string.

0


source


Using the following query, you will get the desired result.

select id 
from test_empty 
group by id 
having TRIM(TRAILING ',' FROM group_concat(value))=""

As a result, the group_concat(value)

output will be a concatenated value separated by commas for all identifiers.

Using TRIM(TRAILING ',' FROM group_concat(value))

trailing comma can be removed

Using having

, we can put a condition on the group to get just the ID with all empty.

0


source


An empty string will always be "less than" any non-empty string, so this should do the trick:

 select id from t1
 group by id
 having max(value) = ''
0


source






More articles:


All Articles