nad nad - 2 months ago 8
SQL Question

Add CDept_Id In first query for result

In my first query, I want to get

CDept_Id
. But
CDept_Id
column does not exist in
inward_doc_tracking_hdr
table.

It comes from
inward_doc_tracking_trl
table. like below

SELECT CDept_id
FROM inward_doc_tracking_trl
WHERE ref_mkey IN ( SELECT mkey
FROM inward_doc_tracking_hdr
WHERE doc_no = 'IW/HU/16/42' )


So, From this. I get
CDept_Id
. Now I want to add this in my below query.

SELECT mkey ,
Delivered_By ,
Department_Id ,
( SELECT mkey
FROM erp190516.dbo.emp_mst
WHERE mkey IN ( SELECT employee_mkey
FROM erp190516.dbo.user_mst
WHERE mkey = To_User )
) User_Id ,
Doc_Type ,
Email_Id ,
Ref_No ,
CONVERT(VARCHAR(25), Ref_date, 103) Ref_date ,
Inward_Amt ,
Remarks ,
party_name ,
disp_through
FROM erp190516.dbo.inward_doc_tracking_hdr ,
CDept_id -- add CDept_id here
WHERE doc_no = 'IW/HU/16/42'
AND Status_Flag = '13'


How to add this

UPDATE

inward_doc_tracking_hdr
mkey is equal to
inward_doc_tracking_trl
ref_mkey

Answer

It is reading the magic glass bulb, but I think you might nead an INNER JOIN to the other table using the mkey and ref_mkey as link:

Select hdr.mkey
      ,hdr.Delivered_By
      ,hdr.Department_Id 
      ,hdr.Doc_Type,Email_Id
      ,hdr.Ref_No
      ,convert(varchar(25),hdr.Ref_date,103) Ref_date
      ,hdr.Inward_Amt
      ,hdr.Remarks
      ,hdr.party_name
      ,hdr.disp_through
      ,trl.CDept_Id
from erp190516.dbo.inward_doc_tracking_hdr AS hdr
inner join erp190516.dbo.inward_doc_tracking_trl AS trl on hdr.mkey=trl.ref_mkey
 where hdr.doc_no = 'IW/HU/16/42'  
 and hdr.Status_Flag = '13'

UPDATE ...even more guessing...

--First CTE to get the partioned order of CDept_Id

;WITH OrderedCDept AS
(
    SELECT ROW_NUMBER() OVER(PARTITION BY ref_mkey ORDER BY CDept_Id DESC) AS SortInx
          ,ref_mkey
          ,CDept_Id
    FROM erp190516.dbo.inward_doc_tracking_trl
)

--Second CTE to use TOP 1 WITH TIES to fetch all first rows

,LatestCDept AS
(
    SELECT TOP 1 WITH TIES *
    FROM OrderedCDept
    ORDER BY SortInx
)

--Now use the second CTE instead of the table to join

Select hdr.mkey
      ,hdr.Delivered_By
      ,hdr.Department_Id 
      ,hdr.Doc_Type,Email_Id
      ,hdr.Ref_No
      ,convert(varchar(25),hdr.Ref_date,103) Ref_date
      ,hdr.Inward_Amt
      ,hdr.Remarks
      ,hdr.party_name
      ,hdr.disp_through
      ,trl.CDept_Id
from erp190516.dbo.inward_doc_tracking_hdr AS hdr
inner join LatestCDept AS trl on hdr.mkey=trl.ref_mkey
 where hdr.doc_no = 'IW/HU/16/42'  
 and hdr.Status_Flag = '13'