Select SQL query that fill the missing value from another select

1
votes

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.

2
sqlhivehql
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

2 Answers

2
votes

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;
0
votes

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;