2

I'm not sure the title accurately reflects my question. I have an existing Rate table that has an identity column key and contains a [Rate] column. col1 + col2 + col3 don't uniquely identify a row:

+--------+------+------+------+------+
| RateId | col1 | col2 | col3 | Rate |
+--------+------+------+------+------+

I have a new table that needs to match a rate. The new table has 3 of the required columns to make a match but those columns will return a number of rows in the Rate table. To uniquely identify a rate for the new table I need to match on MaterialTypeId and UnitTypeId where UnitTypeId can be null.

+------------+------+------+------+----------------+------------+
| MaterialId | col1 | col2 | col3 | MaterialTypeId | UnitTypeId |
+------------+------+------+------+----------------+------------+

What is the best approach to resolving this? I could add the two additional columns to the Rate table but that would not be relevant for the current uses of the Rate table, i.e. the two additional columns would be null for all existing rows.

I could introduce an intermediate table and join on MaterialTypeId & UnitTypeID which would return multiple rows and then join using the existing columns to uniquely identify the rate:

+--------+----------------+------------+
| RateId | MaterialTypeId | UnitTypeId |
+--------+----------------+------------+

Is that the correct approach?

1
  • A reference table as you mentioned would work fine. It is mostly a case-by-case scenario. Why would it not be relevant for the current use of the rate table? Could you not uniquely identify as you would if you were joining through the intermediary table? – SQLserving Jun 16 '20 at 20:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.