SQL join on field portion

I asked this 28538443 question last week and created a view.  It worked great until I spotted duplicates.  That join turned out to be on a field that was not unique in the describing (is that  the correct term?) table.  I need to join the table on a field that will be unique but will have extra data in it.  One table (the describing table) Production_Groups has the field Planner_ID with records containing the unique numbers 01,50-68 among other fields and will grow with only unique numbers to match other groups that get added to our production process.  The other table, Account_Types has the field ACCTYP_104 with the unique records 0-Z, D01,D50-D68,E01,E50-E68 and the same for H, L, N, O, P, and R among other fields.  The fields like D01 refers to the type of work done, D, E, ... R and the planner 01, 50-68.  The data is not normalized I know but it is what I have to work with.  My question is can I make a view to two table in different databases on only a portion of a field?  I would want to join HWPL.dbo.Production_Groups.Planner_ID to the right 2 characters of MAXRM.dbo.Account_Types.ACCTYP_104 when the field is 3 characters.  That's not going to happen is it?
gibneytAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Kyle AbrahamsConnect With a Mentor Senior .Net DeveloperCommented:
Convienently there's a "right" function

select * from 
HWPL.dbo.Production_Groups PG
join  MAXRM.dbo.Account_Types AT on PG.Planner_ID = right(AT.ACCTYP_104, 2)

Open in new window

0
 
gibneytAuthor Commented:
Excellent.  I knew about RIGHT but not that it could be used in a JOIN.  Thanks very much!
0
All Courses

From novice to tech pro — start learning today.