Mikkel Mikkel - 6 months ago 13
SQL Question

How to create a 3rd column based on 2 columns from two tables [inner and outer join]


  • I have a lot of rows in 'table alpha' where some of these rows occurs in 'table beta' as well.

  • Both row alpha and beta have a date column.


    • However, when i select both date columns the rows from table alpha that do not occur in row B will have a null-value assigned to them.





My problem is that I want to make a third date-column that will use the dates from table beta if they are not null. If they are null, it should use the dates from table alpha.

Answer

Since you have not provided any info about your data model. I have given you a rough draft based on your problem.

Select 
colA,
colB,
...,
alpha.date1,
beta.date2,
COALESCE(beta.date2,alpha.date1) date3
from 
alpha
left outer join beta (your keys)