Solved

SQL join on field portion

Posted on 2014-10-22
2
143 Views
Last Modified: 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?
0
Comment
Question by:gibneyt
2 Comments
 
LVL 39

Accepted Solution

by:
Kyle Abrahams earned 500 total points
ID: 40397304
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
 

Author Closing Comment

by:gibneyt
ID: 40397648
Excellent.  I knew about RIGHT but not that it could be used in a JOIN.  Thanks very much!
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now