Here's an example I concocted yesterday.

select substring(replace(replace(h.descr,'-REG',''),'-TEM',''),1,30) Dept,
j.emplid EmplId,
substring(p.last_name,1,30) LastNm,
substring(p.first_name,1,30) FirstNm,
p.ssn SSN,
convert(char(10),e.hire_dt,110) HireDt,
case
when isnull(c.emplid,'') = '' then ''
else '05'
end c05
from prod..ps_job j,
dc.dbo.emplS_chks_2005 c,
prod.dbo.ps_personal_data p,
prod.dbo.ps_employment e,
prod.dbo.ps_al_dept_tbl h
where j.effdt = (select max(x.effdt)
from prod.dbo.ps_job x
where x.emplid = j.emplid)
and j.effseq = (select max(y.effseq)
from prod.dbo.ps_job y
where y.emplid = j.emplid
and y.effdt = j.effdt)
and p.emplid = j.emplid
and c.emplid =* j.emplid
and e.emplid = j.emplid
and h.department_nbr = j.home_department
order by j.deptid, j.emplid


-----Original Message-----
From: Indra Timilsina
[mailto:mssqldba-ezmlmshield-x52526195.[Email address protected]
Sent: Friday, February 24, 2006 8:08 AM
To: LazyDBA Discussion
Subject: creating query from two different database.


Hi there,



I am looking for information on how to create query with tables from two
different databases which are in same server



Like table A is in database TEST1

And table B is in database TEST2



I need to select A.F1, A.F2, A.F3 AND B.F1, B.F2, B.F3 WHERE A.F4=B.F4





Thanks.





---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html



--
This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.

Douglas County, Oregon
www.co.douglas.or.us


MS Sql Server LazyDBA home page