CODEWITHSUNDEEP
hivehiveql
Jan Sila
Jan Sila

Reputation: 1593

Select a value when other column has two specific values

I cannot find a way to make this work: I need to get all id1 that have id2 ''(empty string) and also one at least non-empty id2.

I get this as: SELECT id1, id2 FROM mytable WHERE id1 = ... GROUP BY id1,id2

    id1                                              id2
1   b2-04af1ab73705-fb8000-006bfb81a78e5e5920   
2   b2-04af1ab73705-fb8000-006bfb81a78e5e5920        5835113447594857813

So now I need to get this id1. I tried lots of things, but closest I got is getting true or false if the condition is met, but cannot get the respective id1 with it - I need it as I want to run this on larger set (not mytable is subsetted on id1=...).

When I try it like this: 

SELECT if(sum(if(tab1.id1 != '', 1, 0)) < count(tab1.id2), tab1.id1,false) as myguy 
        FROM
            (   SELECT id1,id2 
                    FROM mytable 
                WHERE id1 = 'b2-04af1ab73705-fb8000-006bfb81a78e5e5920' 
                       GROUP BY visitorid,platformvisitorid
            ) AS tab1

, I get this error: Line 1:103 Expression not in GROUP BY key 'visitorid' and cannot find a way around it.

Any ideas, please?

EDIT:

Solved it with help from Dennis as follows:

SELECT tab4.id1 FROM 

(SELECT tab1.id1 FROM (SELECT id1, id2 FROM mytable GROUP BY id1, id2) tab1 WHERE id2!='') tab3

INNER JOIN

(SELECT tab2.id1 FROM (SELECT id1, id2 FROM mytable GROUP BY id1, id2) tab2 WHERE id2='') tab4
 ON (tab3.id1 = tab4.id1)

Upvotes: 0

Views: 83

Answers (2)

Dennis Jaheruddin
Dennis Jaheruddin

Reputation: 21563

Edit

It is apparently possible to do this in 1 go, which prevents polluting your DB. See the answer by @Dudu Markovitz

It is probably possible to build this into 1 statement and thus eliminate the creation of temp tables (or view).

CREATE TABLE tempa AS SELECT DISTINCT id1 from mytable where id2 = '';
CREATE TABLE tempb AS SELECT DISTINCT id1 from mytable where id2 != '';

SELECT tempa.id1 FROM tempa INNER JOIN tempb on tempa.id1 = tempb.id1;

Note that 'not empty' is not a clear definition, so you may need to adjust the where statement in the second line to suit your needs.

Upvotes: 0

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

select      id1
from        mytable 
group by    id1
having      count(case when id2 =  '' then 1 end) > 0
        and count(case when id2 <> '' then 1 end) > 0

Upvotes: 2

Related Questions