Retrieves a column in the join from the same table but from the different database

advertisements

I have two database.

a. DB_1 b. DB_2

let say, I am currently using DB_1 for my below query

Select top 1 a.mkey,convert(varchar(255), a.ref_date,103) as REF_DATE,  cd.type_desc as DOC_TYPE,  a.doc_no as INWARD_NO,
                       cr.type_desc as dept_received, e.emp_name as EMP_RECEIVED,
                       convert(varchar(255), a.doc_date,103) as date,
                       a.to_user, a.No_of_pages, Ref_No,
                       e.emp_name as NAME,
                       coalesce(e.Email_Id_Official, '[email protected]') EMAILID, a.Party_Name
                from inward_doc_tracking_hdr a left join
                     type_mst_a cd
                     on a.doc_type = cd.master_mkey left join
                   type_mst_a cr
                     on a.dept_received = cr.master_mkey
                     and cr.type_code='D1'
                     left join  emp_mst e
                     on a.emp_received = e.mkey
                     where a.emp_received is not null and
                        a.mkey = 146

Now what I want is. I want to join the same table from another database(DB_2) how to do that ??

UPDATE

My another db column name is Inward_ref_key and its table name is inward_doc_tracking_hdr


As discussed. Please see answer below.

SELECT TOP 1 a.mkey,
    CONVERT(VARCHAR(255), a.ref_date,103) AS REF_DATE,
    cd.type_desc AS DOC_TYPE,
    a.doc_no AS INWARD_NO,
    cr.type_desc AS dept_received,
    e.emp_name AS EMP_RECEIVED,
    CONVERT(VARCHAR(255), a.doc_date,103) AS DATE,
    a.to_user,
    a.No_of_pages,
    Ref_No,
    e.emp_name AS NAME,
    COALESCE(e.Email_Id_Official, '[email protected]') EMAILID,
    a.Party_Name,
    doc_no = (SELECT TOP 1 doc_no FROM erp190516.dbo.inward_doc_tracking_hdr WHERE mkey = a.inward_ref_key)
    OLD_DOC_NO
FROM inward_doc_tracking_hdr a
LEFT JOIN type_mst_a cd
    ON cd.master_mkey  = a.doc_type
LEFT JOIN type_mst_a cr
    ON cr.master_mkey = a.dept_received
    AND cr.type_code='D1'
LEFT JOIN emp_mst e
    ON  e.mkey = a.emp_received
WHERE a.emp_received IS NOT NULL
AND a.mkey = 146