Alternative logic for a subquery as part of a condition

Question

I have two tables:

      T1

key  code1  code2  code3
  1      A      A      A
  2      B      B      G
  3      A      B      C
  4      C      C      C
  5      D      E      F
  6      E      E      E
  7      A      D      G
  8      G      G      G


      T2

code  class1  class2  class3 
   A       1       0       0      
   B       0       1       0      
   C       0       1       0      
   D       1       1       0      
   E       0       0       1      
   F       0       1       0      
   G       1       0       0

I want to write a query like...

create table T3 as
select key, case 
            when code1 in (select code from T2 where class1 = 1) or
                 code2 in (select code from T2 where class1 = 1) or
                 code3 in (select code from T2 where class1 = 1) 
                 then 1 else 0
            end as class1,
            case 
            when code1 in (select code from T2 where class2 = 1) or
                 code2 in (select code from T2 where class2 = 1) or
                 code3 in (select code from T2 where class2 = 1) 
                 then 1 else 0
            end as class2,
            case 
            when code1 in (select code from T2 where class3 = 1) or
                 code2 in (select code from T2 where class3 = 1) or
                 code3 in (select code from T2 where class3 = 1) 
                 then 1 else 0
            end as class3
from T1

It is basically saying for each key in T1, look to see if the corresponding code in T2 has a 1 for each class. If so, then the new column is 1.

The problem is, I am using a version of HiveQL that doesn't support subqueries in conditional statements like this. Is there an alternative method to achieve the same result set? I was thinking something like joining, but I'm not sure of the best way to go about that in this case due to T2 not having a corresponding key.

For reference, the result set would be

      T3

key  class1  class2  class3
  1       1       0       0
  2       1       1       0
  3       1       1       0
  4       0       1       0
  5       1       1       1
  6       0       0       1
  7       1       1       0
  8       1       0       0

Mihai · Answer 1 · 2016-01-05T16:16:15.293

1

SELECT `key`,
 MAX(CASE WHEN  class1 = 1 THEN 1 ELSE 0 END) as class1,
 MAX(CASE WHEN  class2 = 1 THEN 1 ELSE 0 END) as class2,
 MAX(CASE WHEN  class3 = 1 THEN 1 ELSE 0 END) as class3
FROM t1 JOIN t2 ON t1.code1=t2.code 
OR  t1.code2=t2.code 
OR  t1.code3=t2.code 
GROUP BY `key`

Assuming mysql, key is a reserved word, you need backticks

edited Jan 05 '16 at 16:16

answered Jan 05 '16 at 15:50

Mihai

26,325
7
66
81

I don't believe this will work, since you're going to get more than one result for each ID. – Tom H Jan 05 '16 at 16:02
Maybe a distinct or a max on each case solves the problem. – Jorge Campos Jan 05 '16 at 16:03
YEah im guessing this will give more rows than necessary hold on – Mihai Jan 05 '16 at 16:03

score 1 · Answer 2 · answered Jan 05 '16 at 16:11

1

This matches your expected results:

SELECT
    [key],
    MAX(T2.class1) AS class1,
    MAX(T2.class2) AS class2,
    MAX(T2.class3) AS class3
FROM
    dbo.T1
LEFT OUTER JOIN dbo.T2 ON T2.code IN (T1.code1, T1.code2, T1.code3)
GROUP BY
    [key]

answered Jan 05 '16 at 16:11

Tom H

46,766
14
87
128

Unfortunately HIVE 0.12 does not support join conditions that aren't equality conditions, meaning this will not work. The other answer won't work either as it doesn't support OR. Would I have to create 3 different tables and then join them together? @TomH – Danzo Jan 06 '16 at 15:37
Yuck. What a restrictive language. It sounds like multiple joins is the only possibility then. – Tom H Jan 06 '16 at 15:42

Alternative logic for a subquery as part of a condition

2 Answers2