Search code examples
sqljoinsubqueryinner-joinunion

Use SELECT subquery within UNION

I have two tables:

Table 1

Table 1

Table 2

Table 2

I am trying to write a query to SELECT all records for both tables, using UNION (columns ID, Date and Amount). The tables are linked by ID. When selecting the records in Table 2 however during the UNION, if the related ID in Table 1 has a True or False value of TRUE, I want to change the Date of the Table 2 record to the date in Table 1, ultimate acheiving this:

Output

Is this possible?

Solution

  • The 2nd query of UNION ALL should be a join of the tables:

    select id, date, amount 
from Table1
union all
select 
  t2.id,
  case when t1.trueorfalse = 'TRUE' then t1.date else t2.date end
  t2.amount
from Table2 t2 inner join Table1 t1
on t1.id = t2.id

    The CASE expression will return either the date from Table1 or from Table2.
    If your database supports the Boolean data type maybe you should use TRUE instead of 'TRUE'.