Skip to content
Advertisement

Build combined table from 2 uneven tables

I have 2 uneven tables which I am trying to build into 1 table, each table has a date and ID to join with, the issue is that sometime 1 table can have rows with no matching date in 2nd table

Initially table 2 seemed to always have an entry for table 1 so I was doing a left join which was working, but then I found the issue where the right table had an entry for some dates but table 1 had no matching dates (i.e. 19 and 20) I was joining on DATE and ID

enter image description here enter image description here enter image description here

Do I need to build some sort of table with all the dates in?

Advertisement

Answer

One option would be use full outer join as follows and use case statements for filling up values when records are not present in table1 or table2 as below.

select case when a.date is not null then 
                 a.date
            else b.date
        end as date
        ,case when a.id is not null then 
                   a.id
              else b.id
          end as id
       ,case when a.name is not null then 
                   a.name
              else b.[full name]
          end as name 
        ,a.[Time Logged In]
        ,b.vol
  from table1 a
full outer join table2 b
     on a.date=b.date
    and a.id=b.id 
3 People found this is helpful
Advertisement