Developer Developer - 5 months ago 15
SQL Question

How to Select Two tables without union and union all?

Table1:

Id | Text | Parent Id | Number
**************************************
101 |robber | 201 | 1
102 |devel | 202 | 1
103 |programmer | 203 | 3


Table 2
Id | TO id | Parent Id | Number
**************************************
102 |355 | 201 | 1
104 |366 | 202 | 2
105 |377 | 203 | Null


I need to join two tables without using
Union
and
union All


Out Put Like:
(Both table columns are same expect one To Id that columns add to last )

Id | Text | Parent Id | Number | To Id
101 |robber | 201 | 1 | Null
102 |devel | 202 | 2 | null
103 |programmer | 203 | 3 |Null
102 |Null | 201 | 1 |355
104 | Null | 202 | 2 | 366
105 |Null | 203 | null | 377

Answer

Try full joint

select isnull(a.id,b.id) as id, 
    a.Text1,isnull(a.ParentId,b.ParentId) parentid,
    isnull(a.Number,b.Number) numm,TOid 
 from @t a 

 full join @t1 b on a.Id=b.Id and a.ParentId=b.ParentId

data

declare @t table (Id int,Text1 varchar(50),ParentId int, Number int) insert into @t
(Id,Text1,ParentId, Number) values
(101 ,'robber'     , 201       , 1),
(102 ,'devel'      , 202       ,  1),
(103 ,'programmer' , 203       , 3)

declare @t1 table (Id int,TOid int,ParentId int, Number int) insert into @t1
(Id,TOid,ParentId, Number) values
(102 ,355        , 201       , 1),
(104 ,366        , 202       ,  2),
(105 ,377        , 203       , Null)