stack stack - 3 months ago 8
SQL Question

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'

Answer
SELECT a.mkey, 
    b.mkey AS RefMkey, 
    a.doc_no, 
    a.doc_date, 
    e.type_desc AS DocType, 
    c.first_name + ' ' + c.last_name AS CurrentUser,
    d.Type_desc AS Department, 
    b.remarks, 
    docType.type_desc AS  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
LEFT JOIN
    (
        SELECT type_desc,
            Add_IInfo1 
        FROM type_mst_a
        WHERE type_code = 'S2'
    ) AS docType
    ON docType.Add_IInfo1 = b.NStatus_flag
WHERE a.mkey = 227394
Comments