Mapping table columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Mapping table columns

Author

Topic

magikminox
Starting Member

27 Posts

Posted - 2008-04-18 : 04:22:05

Hi Guys

I have a table that contains codes for commodities.Some of the codes in this table have changed and some of them have not.So now i want to design a solution that enable me to map the new codes in a different mapping table to the old ones in the other table.I also want to retain the old codes because most of the archived data used the old codes.

Where there is no new code, the current code is being retained.How do i design my table and queries so that i can use the new codes as if i was using the old code.I want to select products with a certain code but using the new code and mapping to the old codes or vice versa.

The structure of the data is like this.
Code Name
AA AA
AL Aluminium
ALM ALM
ALT Aluminium in tonnes
AR AR
AUD Australian Dollars
AUJPY AUJPY
CAQ CAQ
CC CC
CCF CCF
CER Carbon Emmission Reduction

The mapping table is like this:
XAA AA
XAL AL
XMA ALM
XAL ALT
XAR AR

In god we trust,everything else we test.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-18 : 04:29:55

The new table will contain only the modified codes along with the old codes and code id if present in current table.

in queries take a left outer join with this table from current table on code or id and use it in filter condition or retrieval of values

ex:

select...
       COALESCE(n.NewCode,c.Code)
......
FROM YourCurrentTable c
LEFT JOIN NewCodeTable n
ON n.Code=c.Code
WHERE ...
..
AND COALESCE(n.NewCode,c.Code)=@Code

where @Code is parameter passed from your front end applications which passes latest code value.

magikminox
Starting Member

27 Posts

Posted - 2008-04-18 : 04:58:22

My Current query is as below.I am joining 3 tables and returning the result.But one of the table (Commodity)is the one that has some of its codes changed and i want to modify this query so that i can still get correct results.How do i include the LEFT JOIN as you said above into this query. so i can include the mapping table.

SELECT TR.ClientId,TR.TradeId,TR.TransactionDate,TR.BookCode,TR.ExpiryDate,TR.TransactionCode
FROM Transaction TR
INNER JOIN Book BO ON BO.Code = TR.BookCode
INNER JOIN Commodity CO ON CO.Code = TR.CommodityCode

Thanks very much.

In god we trust,everything else we test.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-18 : 05:05:45

[code]SELECT TR.ClientId,TR.TradeId,TR.TransactionDate,TR.BookCode,TR.ExpiryDate,TR.TransactionCode
FROM Transaction TR
INNER JOIN Book BO ON BO.Code = TR.BookCode
INNER JOIN (
SELECT COALESCE(n.NewCode,c.Code) AS Code
FOM Commodity c
LEFT OUTER JOIN NewCommodity n
ON n.Code=c.Code
)CO
ON CO.Code = TR.CommodityCode
[/code]
i guess you will have latest codes for all commodity in your Transaction table

magikminox
Starting Member

27 Posts

Posted - 2008-04-18 : 05:49:42

Yes the Transaction table has the new codes only.
The NewCommodity table is the mapping table with 2 columns only,one for the new codes and the corresponding old codes.The NewCommodity table does not include the codes that did not change.
SELECT .....
FROM Transaction TR
INNER JOIN Book BO ON BO.Code = TR.BookCode
INNER JOIN (SELECT COALESCE(n.NewCode,c.Code) AS Code
FROM Commodity c
LEFT OUTER JOIN NewCommodity n
ON n.Code=c.Code
)CO
ON CO.Code = TR.CommodityCode

This version of the query is giving me about 28423 results when i expect only 11700 or so
Am i getting something wrong?.

Thanks.

In god we trust,everything else we test.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-18 : 05:52:23

quote:
Originally posted by magikminox

Yes the Transaction table has the new codes only.
The NewCommodity table is the mapping table with 2 columns only,one for the new codes and the corresponding old codes.The NewCommodity table does not include the codes that did not change.
SELECT .....
FROM Transaction TR
INNER JOIN Book BO ON BO.Code = TR.BookCode
INNER JOIN (SELECT COALESCE(n.NewCode,c.Code) AS Code
FROM Commodity c
LEFT OUTER JOIN NewCommodity n
ON n.Code=c.Code
)CO
ON CO.Code = TR.CommodityCode

This version of the query is giving me about 28423 results when i expect only 11700 or so
Am i getting something wrong?.

Thanks.

In god we trust,everything else we test.