Remedy Bad Design

Transact-SQL
1

I have a table System Tax Rates:
Tax_Name
Tax_Rate

I have another table Items:
Item_Number
Item _Description
Tax1_name
Tax2_name
Tax3_name
Tax4_name

I need a view to show:
Item_Number
Item _Description
Tax1_name
Tax1_rate
Tax2_name
Tax2_rate
Tax3_name
Tax3_rate
Tax4_name
Tax4_rate

Is this possible without Creating another table to hold the contents and four individual queries to load and populate the new table?

Any suggestions are welcomed!!

2 
SELECT i.Item_Number, i.[Item Description], 
    i.Tax1_name, str1.Tax_Rate,
    i.Tax2_name, str2.Tax_Rate,
    i.Tax3_name, str3.Tax_Rate,
    i.Tax4_name, str4.Tax_Rate 
FROM Items i
LEFT OUTER JOIN [System Tax Rates] str1 ON str1.Tax_Name = i.Tax1_name
LEFT OUTER JOIN [System Tax Rates] str2 ON str2.Tax_Name = i.Tax2_name
LEFT OUTER JOIN [System Tax Rates] str3 ON str3.Tax_Name = i.Tax3_name
LEFT OUTER JOIN [System Tax Rates] str4 ON str4.Tax_Name = i.Tax4_name
2 Likes
3

Awesome! I didn't know you could do this in SQL (have multiple Joins to the same table) Thank you for your solution.

4

If you are have a lot of queries that need the Tax Lookups you might consider creating a VIEW.

CREATE VIEW Items_View
WITH SchemaBinding    -- Optional, but recommended
AS
SELECT i.Item_Number, i.[Item Description], 
    i.Tax1_name, 
    i.Tax2_name, 
    i.Tax3_name, 
    i.Tax4_name, 
   ... any other columns from [Items] table ...
    -- Lookup columns:
    str1.Tax_Rate as [Tax_Rate1],
    str2.Tax_Rate as [Tax_Rate2],
    str3.Tax_Rate as [Tax_Rate3],
    str4.Tax_Rate as [Tax_Rate4]
FROM Items i
LEFT OUTER JOIN [System Tax Rates] str1 ON str1.Tax_Name = i.Tax1_name
LEFT OUTER JOIN [System Tax Rates] str2 ON str2.Tax_Name = i.Tax2_name
LEFT OUTER JOIN [System Tax Rates] str3 ON str3.Tax_Name = i.Tax3_name
LEFT OUTER JOIN [System Tax Rates] str4 ON str4.Tax_Name = i.Tax4_name

You can then do

SELECT Item_Number, [Item Description], Tax_Rate1, Tax_Rate2, ...
FROM Items_View

The sequence of the columns is not important - you could have TaxName, TaxRate as Scott had them. We prefer to have ALL the columns from the original table, and then all the various Lookup Tables. If we add a column to the underlying table we add that in the correct place so if, for example, we do a SELECT * we get the same columns in the same order as the underlying table. We don't "rely" on the column sequence (nor should you!) but we do find it "easier" if they are consistent.

You will need to provide a unique alias name for each Lookup Column (which I've added in my example, lifted from Scott's)

5

Sorry, fixed a typo above (I know that the forum generates an EMail with the reply, so it seems to me I need to write a comment if I change something otherwise O/P might cut&paste code from the email ... which won't work :frowning: )