Creating a new column using the result set from union all – sql

I have TWO simple VIEWS with columns PID and NAME respectively. Here’s how they are connected to each other.

View1:

PID NAME

Comp1 , C1

Comp2 , C2

View2:

PID NAME ParentPID

Pool1 , P1 , Comp1

Pool2 , P2 , Comp2

I want to create a view with below columns by using UNION ALL statement (I guess that’s the easiest approach)

PID , NAME , PID:NAME

Comp1 , C1 , C1

Comp2 , C2 , C2

Pool1 , P1 , C1:P1

Pool2 , P2 , C2:P2

If I just use PID and NAME columns, the union all statement would work correctly. for example:

SELECT comp.PID, comp.NAME

from View1 comp

UNION ALL

SELECT pool.PID, pool.NAME

from View2 pool;

But it fails when I tried to create a new column using UNION ALL as shown below.

SELECT comp.PID, comp.NAME,comp.NAME as Comp_pool_NAME

from View1  comp

UNION ALL

SELECT pool.PID, pool.NAME, concat(comp.NAME||':',pool.NAME) as Comp_pool_NAME

from View2  pool;

Answer

The two queries in a UNION ALL are independent of each other. You can’t reference one inside the other. It looks like what you want is:

all rows from View1
union all
all rows from View2 with a column from View1

So in the second query, you need to join view1 and view2:

select pid, name, pid_name from view1
union all
select v2.pid, v2.name, v1.name || ':' || v2.name
  from view2 v2
 inner join view1 v1 on v1.pid = v2.pid