CODEWITHSUNDEEP
sqloracle-databasedatejoinouter-join
Aser16
Aser16

Reputation: 61

Is there a join in sql that will fill in missing data of one table to the other?

I want to fill in dates that are missing from one table from the dates table that I created.

The first table where i have all the dates is this one:

Dates
01/11/2018
02/11/2018
03/11/2018

The second table with my data is this one:

Name    Value   Date
Go        1     01/11/2018
Go        3     02/11/2018
Ma        2     03/11/2018

My final output should be like this:

Name    Value   Date
Go        1     01/11/2018
Go        3     02/11/2018
Go      null    03/11/2018
Ma      null    01/11/2018
Ma      null    02/11/2018
Ma        2     03/11/2018

I tried a full outer join and it didn't work:

select b.name, a.d_date, b.value
from date_month a 
full outer join namevalue b on a.d_date=b.d_date;

datemonth is the table with the dates and namevalue is the table with the names. The table with the names has at least 300,000 names in with dates and values.

Upvotes: 3

Views: 4753

Answers (3)

Jon Heller
Jon Heller

Reputation: 36817

A partitioned-outer join is a great way to fill gaps in sparse data. This join lets us repeat a join, based on the values in a column. This approach joins more than a regular outer join, but much less than a cross join, and it should be more efficient than a cross join solution.

select name, value, dates.the_date
from dates
left join my_data
    partition by (my_data.name)
    on dates.the_date = my_data.the_date
order by name, the_date;

NAME  VALUE  THE_DATE
----  -----  --------
Go        1  2018-01-11
Go        3  2018-02-11
Go           2018-03-11
Ma           2018-01-11
Ma           2018-02-11
Ma        2  2018-03-11

Sample schema:

create table dates as
select date '2018-01-11' the_date from dual union all
select date '2018-02-11' the_date from dual union all
select date '2018-03-11' the_date from dual;

create table my_data as
select 'Go' name, '1' value, date '2018-01-11' the_date from dual union all
select 'Go' name, '3' value, date '2018-02-11' the_date from dual union all
select 'Ma' name, '2' value, date '2018-03-11' the_date from dual;

Upvotes: 2

tonypdmtr
tonypdmtr

Reputation: 3225

Tried on SQLite3 (adjust accordingly, if needed).

-- setup test tables & data
create table Dates(d);
insert into dates values
('2018-11-01'),
('2018-11-02'),
('2018-11-03');

--The second table with my data is this one
create table dat(name,val,d);
insert into dat values
('Go',1,'2018-11-01'),
('Go',3,'2018-11-02'),
('Ma',2,'2018-11-03');

and the query is:

select name,val,d
  from (select distinct name,dates.d from dat join dates)
  left join dat using(name,d);

Upvotes: 0

Salman Arshad
Salman Arshad

Reputation: 272106

You need to use CROSS JOIN to create a result consisting of 2 names x 3 dates = 6 rows. Then do a LEFT JOIN:

SELECT all_names.name, date_month.date, namevalue.value
FROM (SELECT DISTINCT name FROM namevalue) AS all_names
CROSS JOIN date_month
LEFT JOIN namevalue ON all_names.name = namevalue.name
                   AND date_month.date = namevalue.date

Upvotes: 2

Related Questions