Solved

SQL join on field portion

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

831 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