Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ibredeemed
Starting Member
3 Posts |
 Posted - 2012-08-16 : 11:50:22
|
| I am joining 2 tables from 2 databases and want to join on the empID but the empID in one database has '36' added to the front of the empid. The 2 databases are linked but not sure how to join the 2 tables on the empID. Query listed below:SelectAeven.prim_unit, Aeven.prim_member, persl.empid, persl.lname, persl.fnamefrom polarchivep.archive81.dbo.aevenJoin (SELECT persl.empid, Right(CAST(persl.empid as varchar(8)),5) as emp_id, lname,fname from POLARCHIVEP.archive81.dbo.persl where ag_id = 'pcso' group by persl.empid,Right(CAST(persl.empid as varchar(8)),5) , lname,fname)as CADEmpleft outer JOIN POLARCHIVEP.archive81.dbo.persl ON Aeven.prim_member = persl.empidleft outer join polksql.staffsched.dbo.tblEmployee on CADEmp.emp_id = polksql.staffsched.dbo.tblemployee.emp_payrollID |
|
|
HenryFulmer
Posting Yak Master
110 Posts |
Posted - 2012-08-16 : 13:21:14
|
| Try joining like this:JOIN ON RIGHT(table1.emp_ID,LEN(table1.empiD)-2) = table2.emp_id |
 |
|
|
ibredeemed
Starting Member
3 Posts |
Posted - 2012-08-16 : 13:38:25
|
| This worked. Thank you!! |
|
|
|
ibredeemed
Starting Member
3 Posts |
Posted - 2012-08-16 : 13:54:40
|
| This worked. Thank you!! |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2012-08-17 : 00:17:02
|
| Depending on the size of your data and which table has fewest rowsJOIN ON table1.empiD = table2.emp_id+'36'might be a better choice. Basically put the expression on the smaller of the 2 tables so the larger one can benefit from an index.Note that it might not make a difference becuase of other factors, but keep an open mind anyway! |
|
|
|
|
|
|
|
|
Join Databases