I need some sql query help. I have a table "MAT_001
" with material number as shown in the below example:
I also have a table with Prefix and material type. As seen in below example:
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.
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?