Looking for some help trying to get some information out of my point of sale db. It is a MS Sql 13.0.4001.0 database
I have two tables. A "stock" table and a "stock UDF" table. They look a little like this:
The Stock Table lets call "ST" and the Stock UDF table "SU"
Stock Table has the following columns
SKU, Description, UDF1 ID, UDF2 ID,UDF3 ID,UDF4 ID
The Stock UDF table has the following columns
ID, Description
I want to create a query that returns a record but instead of the ID under the UDF1 ID column I want to get the description from the SU table.
A sample record in the ST currently looks like this
SKU, Description, UDF1 ID, UDF2 ID,UDF3 ID,UDF4 ID
1000 Orange 2 1 3 Null
The SU table looks like this
ID, Description
1 Fruit
2 Salads
3 Desserts
4 Vegetables
5 Raw
6 Cooked
I want to create a query that returns the following
SKU Description UDF1 UDF2 UDF3 UDF4
1000 Oranges Salads Fruit Desserts
Not sure how to do the inner join correctly.
Something like this:
select st.SKU, st.Description, st.[UDF1 Id], st.[UDF2 Id], st.[UDF3 Id], st.[UDF4 Id]
from [Stock] as st inner join [Stock UDF] as su on st.UDF1 ID = su.ID
But doesn't return what I want.
Thanks in advance.