What should I do to join two columns with different name but has the same data type, so that I can get something I need from one of the table?

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

Answer

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

Advertisement

Answer