CODEWITHSUNDEEP
t-sqllookup
MrTelly
MrTelly

Reputation: 14865

Join two different tables based on lookup

We have large (2M+ row) product data tables with 50+ data fields and multiple country specific product codes. I'd like to be able to efficiently join from ProductData to different lookup tables based different code fields. We currently use seperate but very similar SPs to implement the logic and that's leading to version problems when they change. The SPs themselves are typically 200-500 lines implementing complex logic, and 95% of the logic is universal, but most of the join statements are country specific.

A simplified version in Pseudo SQL looks something like this

ProductTable Schema
Id, Data1, Data2, .... , ProductCodeUK, ProductCodeUSA, ProductJapan

SP1_USA
select Id, Data, Data, LookupUSA.Price As Price from ProductTable 
join LookupUSA on ProductTable.ProductCodeUSA = LookupUSA.Code


SP2_UK
select Id, Data, Data, LookupUK.Price As Price from ProductTable
join LookupUK on ProductTable.CodeVersion2 = LookupUK.ProductCodeUK

Note the Code values are often repeated between different countries, so we can't easily merge the Lookup tables together.

Upvotes: 0

Views: 410

Answers (1)

Mark Byers
Mark Byers

Reputation: 837926

Your problem is that your schema is badly designed. You need to normalize this:

ProductCodeUK, ProductCodeUSA, ProductCodeJapan, ProductCodeGermany, ...

You should move this data to a new table with three columns, one of which is a foreign key back to the original table:

Product CountryCode ProductCode

And your lookup table should look like this:

CountryCode ProductCode Price ....

With this new design your joins are always the same. To select different countries you now only need to modify a parameter in the WHERE clause.

Upvotes: 3

Related Questions