Brian Powell Brian Powell - 1 month ago 21
MySQL Question

Impala SQL, return value if a string exists within a subset of values

I have a table where the

id
field (not a primary key) contains either
1
or
null
. Over the past several years, any given part could have been entered multiple times with one, or both of these possible options.

I'm trying to write a statement that will return some value if there is ever a
1
associated with the select statement. There are lots of semi-duplicate rows, some with
1
and some with
null
, but if there is ever a
1
, I want to return
true
, and if there are only
null
values, I want to return
false
. I'm not sure how to code this though.

If this is my
SELECT part,id from table where part = "ABC1234"
statement

part id
ABC1234 1
ABC1234 null
ABC1234 null
ABC1234 null
ABC1234 1


I want to write a statement that returns
true
, because
1
exists in at least one of these rows.

The closest I've come to this is by using a
CASE
statement, but I'm not quite there yet:

SELECT
a1.part part,
CASE WHEN a2.id is not null
THEN
'true'
ELSE
'false'
END AS id
from table.parts a1, table.ids a2 where a1.part = "ABC1234" and a1.key = a2.key;


I also tried the following case:

CASE WHEN exists
(SELECT id from table.ids where id = 1)
THEN


but I got the error
subqueries are not supported in the select list


For the above
SELECT
statement, how do I return 1 single line that reads:

part id
ABC1234 true

vkp vkp
Answer

You can use conditional aggregation to check if a part has atleast one row with id=1.

SELECT part,'True' id
from parts
group by part
having count(case when id = 1 then 1 end) >= 1

To return false when the id's are all nulls use

select part, case when id_true>=1 then 'True'
                  when id_false>=1 and id_true=0 then 'False' end id
from (
SELECT part,
count(case when id = 1 then 1 end) id_true,
count(case when id is null then 1 end) id_false,
from parts
group by part) t