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.
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!
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.
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 removedUsing
having
, we can put a condition on the group to get just the ID with all empty.
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) = ''