Search code examples
sqlmatrixsql-server-2000

Best way to achieve exclusion matrix via query

What is the best way to achieve this exclusion matrix via query.

There are fixed number of products in a table and idea is if a product is sold to a customer (represented by row), the other products (columns) may or may not be sold based on the rule matrix below. The aim is to get products code which are allowed to sold for any given sold product code.

ProductCode|MRLSPN|MRLSPPN|MRLSDF|MRLSPDF|LGS|LGP|HOBN|HODF|HVO|HVOF
MRLSPN     |No    |No     |No    |No     |No |Yes|No  |No  |No |No  
MRLSPPN    |No    |No     |No    |No     |No |No |No  |No  |No |No  
MRLSDF     |No    |No     |No    |No     |No |Yes|No  |No  |No |No  
MRLSPDF    |No    |No     |No    |No     |No |No |No  |No  |No |No  
LGS        |No    |No     |No    |No     |No |Yes|No  |No  |No |No  
LGP        |Yes   |No     |Yes   |No     |No |No |No  |No  |No |No
HOBN       |No    |No     |No    |No     |Yes|Yes|No  |No  |No |No  
HODF       |No    |No     |No    |No     |Yes|Yes|No  |No  |No |No  
HVO        |Yes   |Yes    |Yes   |Yes    |Yes|Yes|Yes |Yes |No |No  
HVOF       |Yes   |Yes    |Yes   |Yes    |Yes|Yes|Yes |Yes |No |No

Ready by row across columns.

Solution

  • Can you change your format from a matrix to an association table like

    Table AdditionalProducts: SoldProductCode AdditionalProductCode

    So your table would look like

    SoldProdCode, Additional ProdCode
MRLSPN, LGP
MRLSDF, LGP

    Now you can simply run a query to say

    SELECT AdditionalProductCode
FROM AdditionalProducts
WHERE SoldProductcode='MRLSPN'

    Edit

    Another benefit of this approach is that what if you give special discounts if you buy MRLSPN you get LGP at 10% off and if you buy MRLSDF you might get 15$ off. With this model you can extend the association table to include additional attributes. This may or may not apply to your model.