sql joins to get records from one table and matching records from other table
Database A on server1 Database B on server 2 from database A i am joining 3 tables to pull the records. Ex: select a.emplyee_name,b.title,c.company_name,a.email_address,c.dept from table a join table b on a.employee_id=b.employee_id join table c on a.employee_id=c.employeeid where c.dept='accounting' From database 2 i am joining 3 tables to pull the records select x.employee_name,y.company_name,z.authority from table x join table y on x.employee_id=y.employee_id join authority z on x.employee_id=z.authority where authority_type='mmm' I want to get the final result where all employees from Database A with department=accounting and having authority_type =mmm My query should pull all the records from database A and only matching records based on employee_id from database 2. Can anyone help me on this. Appreciate your help on this.
you'll have to set up one of your servers as a linked server. after than you should be able to joins the results across servers... ;with rows_a AS( ) , rows_b AS( ) select * from rows_a as a left outer join rows_b as b on a.employee_id = b.employee_id