SQL server inner join not returning Description

Question

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.

Answer 1

You simply need to join to the table with the description multiple times, as follows. Joins are implicitly INNER JOIN, so it's not necessary to state that unless you want to. Note, however, that in this case you must use a LEFT OUTER join type, else where there is no match in the joined table, you will return no rows.

EDIT

I've added COALESCE() functions so that in the event a su.Decription field returns NULL, the query will actually output an empty string instead of the NULL. COALESCE() is a handy function that returns the first non-null value in its set of arguments.

SELECT st.SKU
       ,st.Description
       ,COALESCE(su1.Description, '') [UDF1 Desc]
       ,COALESCE(su2.Description, '') [UDF2 Desc]
       ,COALESCE(su3.Description, '') [UDF3 Desc]
       ,COALESCE(su4.Description, '') [UDF4 Desc]
FROM Stock st
    LEFT JOIN [Stock UDF] su1 ON st.UDF1 = su1.ID
    LEFT JOIN [Stock UDF] su2 ON st.UDF2 = su2.ID
    LEFT JOIN [Stock UDF] su3 ON st.UDF3 = su3.ID
    LEFT JOIN [Stock UDF] su4 ON st.UDF4 = su4.ID