How to join two select query?

Question

Hi Masters,

 

Need your help as I have plan to join two select query in one table, here my query:

 

select

                t1.name,t1.surname,t1.relation,SUM(t1.year) [years]

                ,t2.name,t2.name,t2.surname,t2.relation,SUM(t2.year) [years]

from

    (select 'Jean' [name] , 'Ligas' [surname],'bf' [relation],1 [year]) t1

left join

    (

   select 'Chuks' [name], 'Diaz' [surname],'bf' [relation],1 [year]

                UNION ALL

                select 'Chukoy' [name], 'Diaz' [surname],'bf' [relation],1 [year]

    ) t2

on

    t1.relation = t2.relation

   group by

    t1.name,t1.surname,t1.relation ,t1.year

   ,t2.name ,t2.name,t2.surname,t2.relation

 

The ouput will be:

name	surname	relation	years	name	name	surname	relation	years
Jean	Ligas	bf	1	Chukoy	Chukoy	Diaz	bf	1
Jean	Ligas	bf	1	Chuks	Chuks	Diaz	bf	1

 

 

I want to have this output please help me how:

name	surname	relation	years	name	name	surname	relation	years
Jean	Ligas	bf	2	Chukoy	Chukoy	Diaz	bf	1
				Chuks	Chuks	Diaz	bf	1