SQL join on field portion
Posted on 2014-10-22
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?