N K N K - 5 months ago 10
SQL Question

Get One column in join from same table but different database

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, 'test@test.com') 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

Answer

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, 'test@test.com') 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