Solved

SQL join on field portion

Posted on 2014-10-22
2
154 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 40

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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql server insert 12 40
Connection to multiple databases 13 25
SQL Syntax: How to force case sensitive query? 2 44
average of calculation (TSQL) 4 26
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

837 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