How to return a single value from multiple values based on mapping


  • I have the following tables:

    Category (has two columns "Input", "Mapping")

    OJ Orange Juice
    APP Apple Juice

    So OJ should be mapped to Orange Juice and so on.

    Here is the main table: Product

    Product (has the following columns:ID,  ProdGroup) and possible values are:

    1 OJ
    2 OJ|APP
    3 APP|CRN

    Now, what I am trying to do is to join the two tables based on Category.Input vs. Product.ProdGroup; however, the problem is that Product.ProdGroup could have multiple values and in this case, just use the first item for matching.  For example,

    OJ|APP -> Just use OJ, which is mapped to OJ in Category table and it should return Orange Juice

    I was thinking a query like this:

    SELECT PR.ID, PR.ProdGroup, CAT.Mapping FROM Product PR

    INNER JOIN Category CAT ON PR.ProdGroup LIKE '%' + CAT.Input + '%'

    Expected result:

    1 OJ Orange Juice
    2 OJ|APP Orange Juice
    3 APP|CRN Apple Juice

    Unfortunately, it does not seem to work the way I would like.

    Any suggestions would be greatly appreciated.



    Thursday, March 27, 2014 5:48 PM