Left Join Issue

Try this.

Declare @TableA table(SID int,Sub varchar(2));
Declare @TableB table(Sub varchar(2));
Insert into @TableA Values(201,'T1')
Insert into @TableA Values(201,'T2')
Insert into @TableA Values(201,'T5')
Insert into @TableA Values(202,'T2')
Insert into @TableA Values(202,'T3')
Insert into @TableA Values(203,'T4')
Insert into @TableB Values('T1')
Insert into @TableB Values('T2')
Insert into @TableB Values('T3')
Insert into @TableB Values('T4')
Insert into @TableB Values('T5')

;With
CTE_Cjoin
as
(
select * from
(select distinct SID from @TableA) Dis
Cross Join
@TableB

)
select C.SUB,A.SID from CTE_Cjoin C
Left join @TableA A on C.Sub=A.Sub and C.SID=A.SID
Order by C.SID,C.SUB

---

Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer