Hi

I have 2 tables each containing a material type. Table 1 contains material from their 3D application. Table 2 contains material with specific values that is not ours and we cannot rename or edit the data. I need a type of junction or mapping table that can connect the user material to the preset material. for example:

User Material = Wood-MDF
Preset Material = MDF Panel

I figured that i would make this table with 3 fields (ID, UserMaterialID, PresetMaterialID)

How would i then construct a query view / Stored procedure that would return the Preset data values based on the user material id?

does that make sense?

Thanks

Yes, create a Table3 lookup table as you have suggested. You can use parameters in a stored procedure:

https://msdn.microsoft.com/en-us/library/ms345415.aspx

If you want to combine table1 and table2 data, basic query might be something like:

SELECT table1.*, table2.*
FROM table1, table2, table3
WHERE (table1.UserMaterial = table3.UserMaterial)
AND (table2.PresetMaterial = table3.PresetMaterial)

You will have to use JOIN operator, e.g.

select P.* from dbo.PresetData P inner join dbo.CrossReferenceTable CR ON P.PresetMaterialID = CR.PresetMaterialID where CR.UserMaterialId = @MaterialID