Peter Dee Peter Dee -3 years ago 97
SQL Question

SQL server inner join not returning Description

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 Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download