1

I have the table user1 :

id |date
1   2018-01-01    
2   null   
3   2018-01-01    
4   null

and another table user2:

id |date_buy
1   2018-01-01    
1   2018-01-02    
2   2018-01-01    
2   2018-01-02    
3   2018-01-01    
4   2018-01-01

I would like to make a select query that select the id and the date from the table user1 but if the date field is null then it shall take the minimal date_buy for this user and fill the missing with it.

So my first idea was: - make a simple query on the first table SELECT id, date from user1

  • make a simple query on the second table SELECT id, min(date_buy) as date from user2 group by id

  • union the two query and make a distinct where date is not null

Which give something like :

SELECT distinct id, date 
from (SELECT id, date 
      from user1 
      UNION 
      select id, min(date_buy) as date 
      from user2 group by id) 
 where date is not null

But I struggle to shape this and make it work.

EvilTeach
  • 28,120
  • 21
  • 85
  • 141
Omegaspard
  • 1,828
  • 2
  • 24
  • 52
  • `DISTINCT` is not a function, it works on the whole selected rows. Remove those redundant parentheses to make code clearer, i.e. `SELECT distinct id, date from ...`. – jarlh Oct 10 '19 at 12:54

2 Answers2

2

In Hive, I think I would do:

select u1.id, coalesce(u1.date, u2.min_date)
from user1 u1 left join
     (select id, min(date_buy) as min_date
      from user2
      group by id
     ) u2
     on u1.id = u2.id;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

this should work

  select u1.id,COALESCE(u1.date, u2.min_dt) from user1 as u1 
    join 
    ( select id,MIN(date_buy) as min_dt from user2 group by id
    ) as u2 
    on u1.id=u2.id;
Strick
  • 1,512
  • 9
  • 15