Solved

SQL Query partial vlookup

Posted on 2016-07-25
4
64 Views
Last Modified: 2016-07-26
I need some sql query help.  I have a table "MAT_001" with material number as shown in the below example:

Material Table
I also have a table with Prefix and material type.  As seen in below example:

Lookup Table
I want to do a sql query with the MAT_001 table and compared it against table PrefixMat and concatenate "Prefix" and "Type".  If any combination matches the Material# I want the material#, description, prefix, and type shown in the line.

Final Output
Note that the prefix can have between 3 to 5 characters long and type is between 2 to 4 characters long.  So they are not always the first 5 characters from Material Number field.  Any ideas?
0
Comment
Question by:holemania
  • 2
4 Comments
 
LVL 40

Expert Comment

by:Sharath
ID: 41728629
try this
SELECT m.Material, m.Description, pm.Prefix, pm.[Type]
  FROM MAT_001 m
  LEFT JOIN PrefixMat pm
    ON LEFTm.Material LIKE pm.Prefix + pm.[Type] + '%'

Open in new window

0
 
LVL 5

Expert Comment

by:Brian Chan
ID: 41728637
Hi holemania,

Is the Prefix and Type combination are unique? and are they  fixed length (ie. Prefix in 3 characters and Type in 2 characters)

If it is, then try the following:

SELECT * 
FROM [dbo].[MAT_001] A
	JOIN [dbo].[PrefixMat] B
		ON (B.Prefix+B.Type) = LEFT(A.Material#,5)

Open in new window


But I vote for Sharath's solution if that works in your case. That is more flexible.
0
 
LVL 26

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 200 total points
ID: 41728705
So they are not always the first 5 characters from Material Number field.
But are they always the first characters of the Material Number field?
Assuming they are then try this
SELECT m.Material, m.Description, pm.Prefix, pm.[Type]
FROM MAT_001 m
LEFT OUTER JOIN PrefixMat pm ON m.Material LIKE pm.Prefix + pm.[Type] + '%'

Open in new window

 
If not, then just add another '%' to the front of that set of concatenations
SELECT m.Material, m.Description, pm.Prefix, pm.[Type]
FROM MAT_001 m
LEFT OUTER JOIN PrefixMat pm ON m.Material LIKE '%' + pm.Prefix + pm.[Type] + '%'

Open in new window

0
 
LVL 40

Accepted Solution

by:
Sharath earned 300 total points
ID: 41728711
In my query, I have an extra LEFT keyword.

SELECT m.Material, m.Description, pm.Prefix, pm.[Type]
  FROM MAT_001 m
  LEFT JOIN PrefixMat pm
    ON m.Material LIKE pm.Prefix + pm.[Type] + '%'

Open in new window

0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

773 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