How to join all combinations from two tables with a common column

Question

I have a list of suppliers who supply a list of countries. For example:

Supplier A supplies to:

1. USA
2. France
3. China

and another list of suppliers with their suppliers' factories For example:

Supplier A manufactures from:

1. UK
2. Germany

How can I build the list (in rows) of all possible combinations of (suppliers, countries, factories)? I have more than 10.000 suppliers. For example, for Supplier A:

1. USA-UK,
2. USA-Germany
3. France-UK
4. France-Germany
5. China-UK
6. China-Germany

Could someone please help me with this? I have been trying to work on it for weeks using Excel and then Access.

Answer 1

Using SQL Server, because I do not have Access installed, hopefully this is generic enough to be useful to you:

Sample tables and data

CREATE TABLE SupplierCountry
(
    SupplierName varchar(50) NOT NULL,
    CountryName varchar(50) NOT NULL
);

INSERT SupplierCountry
    (SupplierName, CountryName)
VALUES
    ('Supplier A', 'USA'),
    ('Supplier A', 'France'),
    ('Supplier A', 'China');

SupplierCountry

╔══════════════╦═════════════╗
║ SupplierName ║ CountryName ║
╠══════════════╬═════════════╣
║ Supplier A   ║ USA         ║
║ Supplier A   ║ France      ║
║ Supplier A   ║ China       ║
╚══════════════╩═════════════╝

SupplierFactory

CREATE TABLE SupplierFactory
(
    SupplierName varchar(50) NOT NULL,
    CountryName varchar(50) NOT NULL
);

INSERT SupplierFactory
    (SupplierName, CountryName)
VALUES
    ('Supplier A', 'UK'),
    ('Supplier A', 'Germany');

╔══════════════╦═════════════╗
║ SupplierName ║ CountryName ║
╠══════════════╬═════════════╣
║ Supplier A   ║ UK          ║
║ Supplier A   ║ Germany     ║
╚══════════════╩═════════════╝

Query:

SELECT
    SC.SupplierName AS Supplier,
    SC.CountryName AS SupplyCountry,
    SF.CountryName AS FactoryCountry
FROM SupplierCountry AS SC
JOIN SupplierFactory AS SF
    ON SF.SupplierName = SC.SupplierName;

Output:

╔════════════╦═══════════════╦════════════════╗
║  Supplier  ║ SupplyCountry ║ FactoryCountry ║
╠════════════╬═══════════════╬════════════════╣
║ Supplier A ║ USA           ║ UK             ║
║ Supplier A ║ France        ║ UK             ║
║ Supplier A ║ China         ║ UK             ║
║ Supplier A ║ USA           ║ Germany        ║
║ Supplier A ║ France        ║ Germany        ║
║ Supplier A ║ China         ║ Germany        ║
╚════════════╩═══════════════╩════════════════╝