Column returning null values instead of values present

I have a query in which, I have merged a sub query for my requirement.

Here the query is.

select a.mkey, b.mkey RefMkey, a.doc_no, a.doc_date, 
            e.type_desc DocType, c.first_name + ' ' + c.last_name CurrentUser,
        d.Type_desc Department, b.remarks, 
                (select f.type_desc from type_mst_a where f.Add_IInfo1
                 = b.NStatus_flag and f.type_code = 'S2') currentStatus
           from inward_doc_tracking_hdr a
        inner join inward_doc_tracking_trl b
            on a.mkey = b.ref_mkey
        inner join user_mst c
            on c.mkey = b.CUser_Id  
        inner join type_mst_a d
            on d.master_mkey = b.CDept_Id   
        inner join type_mst_a e
            on e.master_mkey = a.doc_type
        inner join type_mst_a f 
            on f.master_mkey = b.NStatus_flag
where a.mkey = 227394

but what happening here is, there are values present but I am getting as NULL

Update

CREATE TABLE [dbo].[Inward_Doc_Tracking_Hdr](
[Mkey] [numeric](18, 0) NOT NULL,
[FModule_ID] [char](1) NOT NULL,
[Comp_Mkey] [numeric](4, 0) NOT NULL,
[Branch_Mkey] [numeric](4, 0) NOT NULL,
[Tran_Type] [varchar](6) NOT NULL,
[Tran_Code] [numeric](8, 0) NOT NULL,
[Tran_No] [numeric](4, 0) NOT NULL,
[Department_ID] [varchar](4) NOT NULL,
[User_Id] [numeric](10, 0) NULL,
[Doc_Type] [numeric](10, 0) NULL,
[Doc_No] [varchar](30) NULL,
[Doc_Date] [datetime] NOT NULL,
[Dispatch_By] [char](1) NOT NULL,
[Status_Flag] [numeric](8, 0) NOT NULL,
[FA_Year] [smallint] NOT NULL,
[Party_Mkey] [numeric](10, 0) NULL,
[To_Department] [numeric](10, 0) NULL,
[To_User] [numeric](10, 0) NULL,
[Inward_Amt] [numeric](18, 3) NULL,
[Ref_No] [varchar](40) NULL,
[Ref_date] [datetime] NULL,
[U_DateTime] [datetime] NOT NULL,
[LastAction_DateTime] [datetime] NULL,
[Remarks] [varchar](255) NULL,
[Delete_Flag] [char](1) NOT NULL,
[Outward_Type] [char](1) NULL,
[Doc_Department] [numeric](10, 0) NULL,
[Party_Name] [varchar](80) NULL,
[Delivered_By] [varchar](30) NULL,
[Doc_Description] [varchar](50) NULL,
[Last_Department] [numeric](10, 0) NULL,
[Last_User] [numeric](10, 0) NULL,
[Approved_Amount] [float] NULL,
[Chq_No] [varchar](50) NULL,
[Chq_dated] [datetime] NULL,
[Chq_Bank] [varchar](40) NULL,
[Chq_Amount] [float] NULL,
[Vendor_MKey] [int] NULL,
[Vendor_Comp_Mkey] [int] NULL,
[Project_Mkey] [numeric](10, 0) NULL,
[Program_mkey] [numeric](10, 0) NULL,
[Payment_MKey] [int] NULL,
[Due_Date] [datetime] NULL,
[Updated_Remarks] [varchar](500) NULL,
[Updated_Bill_no] [varchar](27) NULL,
[Updated_Bill_Date] [datetime] NULL,
[Updated_Bill_Amt] [float] NULL,
[TotalDeductions] [float] NULL,
[Broker_Mkey] [numeric](10, 0) NULL,
[Customer_Mkey] [numeric](10, 0) NULL,
[Payable_Amt] [float] NULL,
[Balance_Amt] [float] NULL,
[Req_Bill_Flag] [char](1) NULL,
[Po_No] [varchar](50) NULL,
[Receipt_No] [varchar](50) NULL,
[Bill_No] [varchar](50) NULL,
[Org_id] [varchar](10) NULL,
[Site_Id] [varchar](50) NULL,
[Site_Name] [varchar](150) NULL,
[Cumulative_Amt] [numeric](18, 2) NULL,
[Email_Id] [varchar](50) NULL,
[Emp_Id] [varchar](6) NULL,
[Disp_through] [varchar](50) NULL,
[Disp_Through_Name] [varchar](100) NULL,
[Last_To_User] [numeric](10, 0) NULL

) ON [PRIMARY]

The query which shows there are result is

select type_desc, * from type_mst_a where type_code = 'S2'