Q:

SQL Query on using join command and union

code prints a table with columns play ID, play title, play year which has values in the table ordered by  play year
I want to add Drama, Comedy, Tragedy tables into as ONE column in the huge table. 
So the table should print like this with values below each column:
P_NO, P_TITLE, P_YEAR, TYPE
TYPE is  
select d.D_TYPE as "TYPE" from DRAMA d where d.D_P_NO=p.P_NO   
UNION
select c.C_TYPE as "TYPE" from COMEDY c where c.C_P_NO=p.P_NO  
UNION    
select t.T_TYPE as "TYPE" from TRAGEDY t where t.T_P_NO=p.P_NO

So my question is how can I add the table TYPE into the below code where the SYNTAX is CORRECT and works because my above declaration is wrong and does not work with JOIN tables 
code:
  select p.P_NO, cast(p. P_TITLE as varchar(15)) as P_TITLE, p.P_YEAR 
  from PLAY p 
  order by p.P_YEAR

A:

May be You are looking for this
select p.P_NO, 
cast(p. P_TITLE as varchar(15)) as P_TITLE, 
p.P_YEAR ,
      (select d.D_TYPE as "TYPE" 
            from DRAMA d 
            where d.D_P_NO=p.P_NO  
      UNION
       select c.C_TYPE as "TYPE" 
            from COMEDY c 
            where c.C_P_NO=p.P_NO  
      UNION    
       select t.T_TYPE as "TYPE" 
            from TRAGEDY t 
            where t.T_P_NO=p.P_NO 
      ) as TYPE
from PLAY p 
order by p.P_YEAR

