Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 81
  • Last Modified:

SQL Query partial vlookup

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
holemania
Asked:
holemania
  • 2
2 Solutions
 
SharathData EngineerCommented:
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
 
Brian ChanDBACommented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
SharathData EngineerCommented:
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now