Solved

SQL Query partial vlookup

Posted on 2016-07-25
4
74 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 41

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 27

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 41

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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

734 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