Lookup multiple values from one ID or product type

Question

I have a requirement to lookup a predefined list of value countries for specific IDs or products.

This is a one to many or in some cases many to many relationship, so a simple CASE statement cannot suffice.

For example, when selecting ID 24553489 it should return a list of 5 valid countries associated with that ID. And so on for other examples, the list can be one or many.

How would I approach this in sql? I am thinking the easiest way is to create a linked table and use that to create the mapping but there may be an easier way.

Can anyone suggest the best way to approach this?

Edit: So for example...

24553489 should only be linked with UK, South Africa, Spain, Italy, France

23343097 should only be linked with South Africa, Spain, Italy

Etc

Thanks.

Answer 1

I can't tell if you're comfortable with the many to many table, please forgive me if I'm stating the obvious - but here is an example of how to do it and a couple of examples of how to use it. Note that I set up a couple of CTEs to represent your existing data (country table and products table), and then a CTE to represent the map that holds the many to many relationship. After that I defined 2 examples, one showing how to use the map to go from a product to countries, and the other to show how to go from a country to products.

Feel free to post in comments if you want further clarification!

with cteProducts as ( --You should already have a Products table
    SELECT * 
    FROM (VALUES (100, 'Gizmos'), (101, 'Widgets'), (102, 'Thingies')
         ) as Products(ProductID, ProductName)
), cteCountries as ( --And already have a Country table
    SELECT * 
    FROM (VALUES ('UK', 'United Kingdom'), ('SA', 'South Africa'), ('SP', 'Spain'), ('IT', 'Italy'), ('FR', 'France')
         ) as Countries(CountryCode, CountryName)
), cteMap as ( --Make a map table that handles the many to many relationship
    SELECT * 
    FROM (VALUES (100, 'UK'), (100, 'SA'), (100, 'IT')
        , (101, 'SA'), (101, 'SP'), (101, 'IT')
        , (102, 'UK'), (102, 'SP')
         ) as ProdMap(ProductID, CountryCode)
), cteProdInSpain as ( -- To use it, you can look for products available in a country
    SELECT P.* 
    FROM cteProducts as P 
        INNER JOIN cteMap as M on M.ProductID = P.ProductID 
        INNER JOIN cteCountries as C on C.CountryCode = M.CountryCode
    WHERE C.CountryName = 'Spain'
), cteWhereBuy as ( -- or where to buy a product
    SELECT P.ProductName, C.CountryName  
    FROM cteCountries as C 
        INNER JOIN cteMap as M on M.CountryCode = C.CountryCode 
        INNER JOIN cteProducts as P on P.ProductID = M.ProductID 
    WHERE P.ProductName = 'Gizmos'
)
--SELECT * FROM cteWhereBuy
SELECT * FROM cteProdInSpain

Answer 2

Thank you all for responses, but I ended up creating a mapping and populating a table from a combination of a cursor and if statements. This is not ideal but without getting too complex this is the only way I could do this.