N K N K - 6 months ago 13
SQL Question

SQL query not returning correct result

I have a query with multiple joins for which

DOC_TYPE
column is coming NULL even if it has some values in it. The query is below

SELECT
a.mkey,
c.type_desc DOC_TYPE,
a.doc_no INWARD_NO,
CONVERT(VARCHAR, a.doc_date, 103) date,
a.to_user,
a.No_of_pages,
Ref_No,
c.type_desc DEPT_RECEIVED,
c.type_desc EMP_RECEIVED,
b.first_name + ' ' + b.last_name NAME,
b.email
FROM
inward_doc_tracking_hdr a
LEFT JOIN
user_mst b ON a.to_user = b.mkey
LEFT JOIN
type_mst_a c ON a.doc_type = c.master_mkey
AND a.dept_received = c.Master_mkey
AND a.emp_received = c.Master_mkey
WHERE
a.to_user = '1279'


The
doc_type
value is
428
and whose desciption comes from

select type_desc
from type_mst_a
where master_mkey = 428


as
Drawing
but when I run the join query I get it as NULL. why ??

I am using SQL Server 2005.

Answer

Following the discussion current version is

SELECT
  a.mkey, c.type_desc DOC_TYPE, a.doc_no INWARD_NO, 
  convert(varchar, a.doc_date,103) date, a.to_user, a.No_of_pages, Ref_No, d.type_desc DEPT_RECEIVED, 
  b.first_name + ' ' + b.last_name SENDER, b.first_name + ' ' + b.last_name   NAME, b.email 
FROM inward_doc_tracking_hdr a 
-- LEFT ?
JOIN user_mst b ON a.to_user = b.mkey 
JOIN type_mst_a c ON a.doc_type = c.master_mkey 
JOIN type_mst_a d ON a.dept_received = d.Master_mkey 
WHERE 
  a.to_user = '1279'

LEFT JOIN is needed if inward_doc_tracking_hdr rows with NULLs or having no matches still must be present in the result. Hope we are now on the right track.