What should I do to join two columns with different name but has the same data type, so that I can g-CodePudding

I have two table, [table a] and [table b]. So basically, I need work_week from [table a] therefore I want to join the columns together, shift_begin_datetime from [table a] and shift_start_datetime from [table b] as both of them have the same data type but they have different name.

[table a]                                   [table b]
| shift_begin_datetime | work_week |        | shift_start_datetime | ........ |
| 2002-06-29 07:00:00  |     34    |        | 2003-07-29 07:00:00  | ........ | 
| 2002-06-30 07:00:00  |     35    |        | 2003-07-30 07:00:00  | ........ | 
| 2002-06-31 07:00:00  |     36    |        | 2003-07-31 07:00:00  | ........ |

I want both of the column to be renamed as shift_start_dt and after combining the result should be something like this.

[result]
| shift_start_dt       |
| 2002-06-29 07:00:00  |           
| 2002-06-30 07:00:00  |            
| 2002-06-31 07:00:00  |      
| 2003-07-29 07:00:00  |
| 2003-07-30 07:00:00  |   
| 2003-07-31 07:00:00  |

And is it possible to display work_week and ........ (representing the rest of the data)?

| shift_start_dt       | work_week |   null?  |
| 2002-06-29 07:00:00  |     34    |   null?  |             
| 2002-06-30 07:00:00  |     35    |   null?  |            
| 2002-06-31 07:00:00  |     36    | ........ |       
| 2003-07-29 07:00:00  |    null?  | ........ |
| 2003-07-30 07:00:00  |    null?  | ........ |    
| 2003-07-31 07:00:00  |    null?  | ........ |

I was also wondering if there is no data for the certain column, will it return as null?

I have tried union all for both of the table into a temp table, but I do not know how can I get work_week out of it. But I think union cant allow me to get work_week out of it, therefore, I'm not sure what else solution I can do. Here's what I did:

select 
    shift_start_datetime
into #datetime
from
    (select distinct 
        shift_begin_datetime as shift_start_datetime
        from table a
        union all
    select distinct 
        shift_start_datetime as shift_start_datetime 
        from table b
    ) as dt

CodePudding user response：

See if this helps you too, COALESCE and FULL OUTER JOIN are used:

SELECT COALESCE(a.shift_begin_datetime, b.shift_start_datetime) AS shift_start_dt,
       a.work_week,
       b.otherFields
FROM tableA AS a
FULL OUTER JOIN tableB AS b 
ON a.shift_begin_datetime = b.shift_start_datetime

Reference: https://learn.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-ver16

CodePudding user response：

You want a union of the two data sets. You can select nulls where a column does not exist in one table, but the other. SQL Server is really picky when it comes to types (which can be really annoying at times), so you probably need to apply CAST in a lot of places to have the selected columns from the tables match.

The final query will lokk something like this:

select
  shift_begin_datetime as shift_start_dt,
  work_week,
  cast(null as varchar(100)) as b_name,
  cast(null as int) as b_amount
from table_a
union all
select 
  shift_start_datetime as shift_start_dt,
  null as work_week,
  name as b_name,
  amount as b_amount
from table_b
order by shift_start_dt;