CODEWITHSUNDEEP
sqlhivesubqueryhiveqlin-subquery
Danzo
Danzo

Reputation: 553

Alternative logic for a subquery as part of a condition

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

Upvotes: 0

Views: 73

Answers (2)

Mihai
Mihai

Reputation: 26804

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

Upvotes: 1

Tom H
Tom H

Reputation: 47402

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]

Upvotes: 1

Related Questions