Task: equi-join-detect (source dataset: Auto-BI)

Your are given a database with related tables used for business intelligence analysis. Your task is to identify all key/foreign-key join relationships between the tables for this database. Recall that a join is a relationship between two tables, that allows rows from one table to be combined with rows from another, based on a set of common join columns.
 
 
No explanation needed. Please return all join relationships in the following JSON format:
{
  "joins": [
    {
      "from_table": "table-1",
      "from_column": "column-1",
      "to_table": "table-2",
      "to_column": "column-3"
    }, 
	...
  ]
}

Tables:

Table: Clients
|   ClientID | ClientName                  | Address1             | Address2   | Town       | County   | PostCode   | Region                   | OuterPostode   |   CountryID | ClientType   | ClientSize   | ClientSince             |   IsCreditWorthy |   IsDealer |
|-----------:|:----------------------------|:---------------------|:-----------|:-----------|:---------|:-----------|:-------------------------|:---------------|------------:|:-------------|:-------------|:------------------------|-----------------:|-----------:|
|          1 | Aldo Motors                 | 4, Scale Street      |            | Uttoxeter  | Staffs   | ST17 99RZ  | East Midlands            | ST             |           1 | Wholesaler   | Large        | 1998-01-04 00:00:00.000 |                1 |          1 |
|          2 | Honest John                 | 99a Baker Street     | NULL       | London     |          | NSW1 1A    | Greater London Authority | EC             |           1 | Dealer       | Large        | 2000-01-01 00:00:00.000 |                0 |          0 |
|          3 | Bright Orange               | 17, Arcadia Way      | NULL       | Birmingham | NULL     | B1 50AZ    | West Midlands            | B              |           1 | Dealer       | Large        | 2005-04-01 00:00:00.000 |                0 |          0 |
|          4 | Cut'n'Shut                  | Grange Avenue        | NULL       | Manchester | NULL     | M1 5AZ     | North West               | M              |           1 | Dealer       | Small        | 2010-01-01 00:00:00.000 |                1 |          0 |
|          5 | Wheels'R'Us                 | Buckingham Drive     | NULL       | London     | NULL     | SE1 4YY    | Greater London Authority | NE             |           1 | Dealer       | Large        | 2007-05-31 00:00:00.000 |                1 |          1 |
|          6 | Les Arnaqueurs              | 33, Rue Des Bleus    | NULL       | Paris      | NULL     | 75010      | NULL                     | NULL           |           2 | Dealer       | Large        | 2003-02-15 00:00:00.000 |                1 |          0 |
|          7 | Crippen & Co                | 1012 Princess Street | NULL       | Glasgow    | NULL     | G1 8GH     | NULL                     | NULL           |           1 | Dealer       | Large        | 2009-10-15 00:00:00.000 |                1 |          0 |
|          8 | Rocky Riding                | 5205 108th Ave       | NULL       | New York   | New York | NULL       | NULL                     | NY             |           3 | Dealer       | Large        | 2009-10-15 00:00:00.000 |                1 |          0 |
|          9 | Voitures Diplomatiques S.A. | NULL                 | NULL       | Geneva     | NULL     | NULL       | NULL                     | NULL           |           6 | Wholesaler   | Large        | 2009-10-15 00:00:00.000 |                1 |          0 |
|         10 | Karz                        | NULL                 | NULL       | Stuttgart  | NULL     | NULL       | NULL                     | NULL           |           4 | Dealer       | Small        | 2006-05-31 00:00:00.000 |                1 |          1 |


Table: Clients1
|   ClientID | ClientName                  | Address1             | Address2   | Town       | County   | PostCode   | Region                   | OuterPostode   |   CountryID | ClientType   | ClientSize   | ClientSince             |   IsCreditWorthy |   IsDealer |
|-----------:|:----------------------------|:---------------------|:-----------|:-----------|:---------|:-----------|:-------------------------|:---------------|------------:|:-------------|:-------------|:------------------------|-----------------:|-----------:|
|          1 | Aldo Motors                 | 4, Scale Street      |            | Uttoxeter  | Staffs   | ST17 99RZ  | East Midlands            | ST             |           1 | Wholesaler   | Large        | 1998-01-04 00:00:00.000 |                1 |          1 |
|          2 | Honest John                 | 99a Baker Street     | NULL       | London     |          | NSW1 1A    | Greater London Authority | EC             |           1 | Dealer       | Large        | 2000-01-01 00:00:00.000 |                0 |          0 |
|          3 | Bright Orange               | 17, Arcadia Way      | NULL       | Birmingham | NULL     | B1 50AZ    | West Midlands            | B              |           1 | Dealer       | Large        | 2005-04-01 00:00:00.000 |                0 |          0 |
|          4 | Cut'n'Shut                  | Grange Avenue        | NULL       | Manchester | NULL     | M1 5AZ     | North West               | M              |           1 | Dealer       | Small        | 2010-01-01 00:00:00.000 |                1 |          0 |
|          5 | Wheels'R'Us                 | Buckingham Drive     | NULL       | London     | NULL     | SE1 4YY    | Greater London Authority | NE             |           1 | Dealer       | Large        | 2007-05-31 00:00:00.000 |                1 |          1 |
|          6 | Les Arnaqueurs              | 33, Rue Des Bleus    | NULL       | Paris      | NULL     | 75010      | NULL                     | NULL           |           2 | Dealer       | Large        | 2003-02-15 00:00:00.000 |                1 |          0 |
|          7 | Crippen & Co                | 1012 Princess Street | NULL       | Glasgow    | NULL     | G1 8GH     | NULL                     | NULL           |           1 | Dealer       | Large        | 2009-10-15 00:00:00.000 |                1 |          0 |
|          8 | Rocky Riding                | 5205 108th Ave       | NULL       | New York   | New York | NULL       | NULL                     | NY             |           3 | Dealer       | Large        | 2009-10-15 00:00:00.000 |                1 |          0 |
|          9 | Voitures Diplomatiques S.A. | NULL                 | NULL       | Geneva     | NULL     | NULL       | NULL                     | NULL           |           6 | Wholesaler   | Large        | 2009-10-15 00:00:00.000 |                1 |          0 |
|         10 | Karz                        | NULL                 | NULL       | Stuttgart  | NULL     | NULL       | NULL                     | NULL           |           4 | Dealer       | Small        | 2006-05-31 00:00:00.000 |                1 |          1 |


Table: Colors
|   ColorID | Color                |
|----------:|:---------------------|
|         1 | Red                  |
|         2 | Blue                 |
|         3 | Green                |
|         4 | Silver               |
|         5 | Canary Yellow        |
|         6 | Night Blue           |
|         7 | Black                |
|         8 | British Racing Green |
|         9 | Dark Purple          |
|        10 | Pink                 |


Table: Colors3
|   ColorID | Color                |
|----------:|:---------------------|
|         1 | Red                  |
|         2 | Blue                 |
|         3 | Green                |
|         4 | Silver               |
|         5 | Canary Yellow        |
|         6 | Night Blue           |
|         7 | Black                |
|         8 | British Racing Green |
|         9 | Dark Purple          |
|        10 | Pink                 |


Table: Countries
|   CountryID | CountryName    | CountryISOCode   |
|------------:|:---------------|:-----------------|
|           1 | United Kingdom | GBR              |
|           2 | France         | FRA              |
|           3 | USA            | USA              |
|           4 | Germany        | DEU              |
|           5 | Spain          | ESP              |
|           6 | Switzerland    | CHE              |


Table: Countries2
|   CountryID | CountryName    | CountryISOCode   |
|------------:|:---------------|:-----------------|
|           1 | United Kingdom | GBR              |
|           2 | France         | FRA              |
|           3 | USA            | USA              |
|           4 | Germany        | DEU              |
|           5 | Spain          | ESP              |
|           6 | Switzerland    | CHE              |


Table: DATE 1
| Date                    |   Month No. | Month Abbr   |   Week no. |   Day no. | Day Name   | Month FullName   |   Quarter no. | Quarter Full   | Quarter Abbr   |
|:------------------------|------------:|:-------------|-----------:|----------:|:-----------|:-----------------|--------------:|:---------------|:---------------|
| 2021-01-01 00:00:00.000 |           1 | Jan          |          1 |         1 | Sun        | January          |             1 | Quarter 1      | Q 1            |
| 2021-01-02 00:00:00.000 |           1 | Jan          |          1 |         2 | Mon        | January          |             1 | Quarter 1      | Q 1            |
| 2021-01-03 00:00:00.000 |           1 | Jan          |          1 |         3 | Tue        | January          |             1 | Quarter 1      | Q 1            |
| 2021-01-04 00:00:00.000 |           1 | Jan          |          2 |         4 | Wed        | January          |             1 | Quarter 1      | Q 1            |
| 2021-01-05 00:00:00.000 |           1 | Jan          |          2 |         5 | Thu        | January          |             1 | Quarter 1      | Q 1            |
| 2021-01-06 00:00:00.000 |           1 | Jan          |          2 |         6 | Fri        | January          |             1 | Quarter 1      | Q 1            |
| 2021-01-07 00:00:00.000 |           1 | Jan          |          2 |         7 | Sat        | January          |             1 | Quarter 1      | Q 1            |
| 2021-01-08 00:00:00.000 |           1 | Jan          |          2 |         8 | Sun        | January          |             1 | Quarter 1      | Q 1            |
| 2021-01-09 00:00:00.000 |           1 | Jan          |          2 |         9 | Mon        | January          |             1 | Quarter 1      | Q 1            |
| 2021-01-10 00:00:00.000 |           1 | Jan          |          2 |        10 | Tue        | January          |             1 | Quarter 1      | Q 1            |


Table: Fact_Sales
|   SalePrice |   SpareParts |   CostPrice |   LaborCost | InvoiceDate             |   Total Cost |
|------------:|-------------:|------------:|------------:|:------------------------|-------------:|
|      178500 |         1400 |       62000 |         325 | 2013-10-02 00:00:00.000 |        60275 |
|       41250 |          400 |       37500 |         325 | 2013-02-02 00:00:00.000 |        36775 |
|       39500 |          400 |       37500 |         325 | 2013-03-02 00:00:00.000 |        36775 |
|       29750 |          400 |       37500 |         325 | 2013-04-02 00:00:00.000 |        36775 |
|      120000 |          400 |       37500 |         325 | 2013-05-02 00:00:00.000 |        36775 |
|       29750 |          400 |       37500 |         325 | 2013-06-02 00:00:00.000 |        36775 |
|       41250 |          400 |       37500 |         325 | 2013-07-02 00:00:00.000 |        36775 |
|       29750 |          400 |       37500 |         325 | 2013-08-02 00:00:00.000 |        36775 |
|       39500 |          400 |       37500 |         325 | 2013-09-02 00:00:00.000 |        36775 |
|       41250 |          400 |       37500 |         325 | 2013-10-02 00:00:00.000 |        36775 |


Table: InvoiceLines
|   InvoiceLineID |   InvoiceID |   StockID |   SalePrice |   LineItem |
|----------------:|------------:|----------:|------------:|-----------:|
|               3 |           1 |         1 |       95000 |          1 |
|               4 |           2 |         2 |      120000 |          1 |
|               5 |           3 |         3 |       88000 |          1 |
|               6 |           4 |         4 |       89000 |          1 |
|               7 |           5 |         5 |       92000 |          1 |
|               8 |           6 |         6 |      102500 |          1 |
|               9 |           7 |         7 |      110000 |          1 |
|              10 |           8 |         8 |      125000 |          1 |
|              11 |           9 |         9 |      130000 |          1 |
|              12 |          10 |        10 |       75000 |          1 |


Table: Invoices
|   InvoiceID | InvoiceNumber                        |   ClientID | InvoiceDate             |   TotalDiscount |   DeliveryCharge |   InvoiceDateKey |
|------------:|:-------------------------------------|-----------:|:------------------------|----------------:|-----------------:|-----------------:|
|           1 | 8B3D7F83-F42C-4523-A737-CDCBF7705B77 |          1 | 2012-10-04 00:00:00.000 |             500 |              750 |         20121004 |
|           2 | 139BEEEF-FF32-4BE9-9EF1-819AC888B85C |          2 | 2012-01-01 00:00:00.000 |               0 |             1500 |         20120101 |
|           3 | D35D72CD-5FF3-4701-A6D1-265A4F4E7CD5 |          3 | 2012-02-02 00:00:00.000 |             750 |             1000 |         20120202 |
|           4 | 2ABAA300-E2A5-4E37-BFCA-7B80ED88A2BD |          2 | 2012-03-03 00:00:00.000 |               0 |             1000 |         20120303 |
|           5 | A1C2D846-EC39-46FA-A399-0C194AAD4DC8 |          5 | 2012-04-04 00:00:00.000 |               0 |             1500 |         20120404 |
|           6 | 1B8F325A-CC41-4BA6-A486-9D44962E40A3 |          4 | 2012-05-04 00:00:00.000 |               0 |             1000 |         20120504 |
|           7 | F1B566F0-D137-4810-B449-575438F3F392 |          3 | 2012-06-04 00:00:00.000 |             750 |              500 |         20120604 |
|           8 | ADFFAC9E-DFF3-4BAB-9EC4-DEE9A2B69350 |          6 | 2012-07-04 00:00:00.000 |            2500 |             1000 |         20120704 |
|           9 | 15A3BC61-82BD-4CCD-8F0B-49EEE59AF4B7 |          1 | 2012-08-04 00:00:00.000 |               0 |             1000 |         20120804 |
|          10 | CFC6726D-1522-4981-BC6B-766AE2C6EED0 |          1 | 2012-09-04 00:00:00.000 |               0 |             1000 |         20120904 |


Table: Stock
|   StockID | Make         | Model         |   ColorID | VehicleType   |   CostPrice |   SpareParts |   LaborCost | Registration_Date       |   Mileage | PurchaseDate            |
|----------:|:-------------|:--------------|----------:|:--------------|------------:|-------------:|------------:|:------------------------|----------:|:------------------------|
|        21 | Rolls Royce  | Silver Seraph |         1 | Saloon        |       62000 |          400 |         951 | 1999-01-01 00:00:00.000 |     52500 | 2012-01-01 00:00:00.000 |
|        23 | Rolls Royce  | Silver Shadow |         8 | Saloon        |       62000 |          400 |         750 | 1985-01-08 00:00:00.000 |     52500 | 2012-01-01 00:00:00.000 |
|        31 | Aston Martin | DB4           |         6 | Coupe         |       75890 |          400 |         147 | 2006-09-08 00:00:00.000 |     52500 | 2012-01-01 00:00:00.000 |
|        89 | TVR          | Tuscan        |         2 | Coupe         |       37500 |          400 |         325 | 2006-09-20 00:00:00.000 |     52500 | 2012-01-01 00:00:00.000 |
|        90 | Jaguar       | XK            |         3 | Saloon        |       37500 |          400 |         325 | 2007-05-09 00:00:00.000 |     52500 | 2012-01-01 00:00:00.000 |
|        91 | TVR          | Cerbera       |         4 | Coupe         |       37500 |          400 |         325 | 2006-09-20 00:00:00.000 |     52500 | 2012-01-01 00:00:00.000 |
|        92 | Jaguar       | XK            |         5 | Convertible   |       37500 |          400 |         325 | 2007-05-09 00:00:00.000 |     52500 | 2012-01-01 00:00:00.000 |
|        93 | TVR          | Tuscan        |         6 | Coupe         |       37500 |          400 |         325 | 2006-09-20 00:00:00.000 |     52500 | 2012-01-01 00:00:00.000 |
|        94 | Jaguar       | XK            |         5 | Saloon        |       37500 |          400 |         325 | 2006-09-20 00:00:00.000 |     52500 | 2012-01-01 00:00:00.000 |
|        95 | TVR          | Cerbera       |         4 | Coupe         |       37500 |          400 |         325 | 2006-09-20 00:00:00.000 |     52500 | 2012-01-01 00:00:00.000 |

 


tables: name: Clients
data: Clients.csv
name: Clients1
data: Clients1.csv
name: Colors
data: Colors.csv
name: Colors3
data: Colors3.csv
name: Countries
data: Countries.csv
name: Countries2
data: Countries2.csv
name: DATE 1
data: DATE 1.csv
name: Fact_Sales
data: Fact_Sales.csv
name: InvoiceLines
data: InvoiceLines.csv
name: Invoices
data: Invoices.csv
name: Stock
data: Stock.csv
label: Invoices
ClientID
Clients1
ClientID
Invoices
ClientID
Clients
ClientID
Clients1
CountryID
Countries2
CountryID
Clients1
CountryID
Countries
CountryID
Stock
ColorID
Colors3
ColorID
Stock
ColorID
Colors
ColorID
InvoiceLines
InvoiceID
Invoices
InvoiceID
InvoiceLines
StockID
Stock
StockID
output: {
    "joins": [
        {
            "from_table": "Invoices",
            "from_column": "ClientID",
            "to_table": "Clients1",
            "to_column": "ClientID"
        },
        {
            "from_table": "Invoices",
            "from_column": "ClientID",
            "to_table": "Clients",
            "to_column": "ClientID"
        },
        {
            "from_table": "Clients1",
            "from_column": "CountryID",
            "to_table": "Countries2",
            "to_column": "CountryID"
        },
        {
            "from_table": "Clients1",
            "from_column": "CountryID",
            "to_table": "Countries",
            "to_column": "CountryID"
        },
        {
            "from_table": "Stock",
            "from_column": "ColorID",
            "to_table": "Colors3",
            "to_column": "ColorID"
        },
        {
            "from_table": "Stock",
            "from_column": "ColorID",
            "to_table": "Colors",
            "to_column": "ColorID"
        },
        {
            "from_table": "InvoiceLines",
            "from_column": "InvoiceID",
            "to_table": "Invoices",
            "to_column": "InvoiceID"
        },
        {
            "from_table": "InvoiceLines",
            "from_column": "StockID",
            "to_table": "Stock",
            "to_column": "StockID"
        }
    ]
}