Solved

SQL Query partial vlookup

Posted on 2016-07-25
4
57 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

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. …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

910 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

21 Experts available now in Live!

Get 1:1 Help Now