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: Customers
| Customer ID   | Customer Name   | Customer Address   | Longitude   | Latitude   |
|:--------------|:----------------|:-------------------|:------------|:-----------|
| C1000         | Jesse Evans     |                    |             |            |
| C1001         | Victor Ramos    |                    |             |            |
| C1002         | Mark Montgomery |                    |             |            |
| C1003         | Dennis Morris   |                    |             |            |
| C1004         | Gregory Simmons |                    |             |            |
| C1005         | Jeremy Vasquez  |                    |             |            |
| C1006         | Anthony Simpson |                    |             |            |
| C1007         | Ernest Rivera   |                    |             |            |
| C1008         | Victor Martinez |                    |             |            |
| C1009         | Bobby Burton    |                    |             |            |


Table: Dates
| Date                    |   Year |   YearOffset | YearCompleted   |   QuarterOfYear | Quarter & Year   |   QuarternYear |   QuarterOffset | QuarterCompleted   |   MonthOfYear |   DayOfMonth | Month Name   | MonthShortName   | Month Initial   | Month & Year   |   MonthnYear |   MonthOffset | MonthCompleted   | MonthEnding             |   DateInt |   DayOfWeek | DayOfWeekName   | Weekday Initial   | Day Type   |   ISO Year |   ISO YearOffset |   ISO QuarterOfYear | ISO Quarter   | ISO Quarter & Year   |   ISO QuarternYear |   ISO QuarterOffset |   ISO Weeknumber | Week & Year   |   WeeknYear |   WeekOffset | WeekCompleted   | WeekEnding              | Fiscal Year   |   FiscalYearOffset | Fiscal Quarter   |   FQuarternYear | IsCurrentFQ   |   Fiscal Period |   FPeriodnYear | IsCurrentFP   |   Fiscal Week | Fiscal Year & Week   |   FWeeknYear | IsCurrentFW   | IsAfterToday   | IsWorkingDay   | IsHoliday   | IsBusinessDay   | IsPYTD   | IsPFYTD   |
|:------------------------|-------:|-------------:|:----------------|----------------:|:-----------------|---------------:|----------------:|:-------------------|--------------:|-------------:|:-------------|:-----------------|:----------------|:---------------|-------------:|--------------:|:-----------------|:------------------------|----------:|------------:|:----------------|:------------------|:-----------|-----------:|-----------------:|--------------------:|:--------------|:---------------------|-------------------:|--------------------:|-----------------:|:--------------|------------:|-------------:|:----------------|:------------------------|:--------------|-------------------:|:-----------------|----------------:|:--------------|----------------:|---------------:|:--------------|--------------:|:---------------------|-------------:|:--------------|:---------------|:---------------|:------------|:----------------|:---------|:----------|
| 2018-01-01 00:00:00.000 |   2018 |           -3 | True            |               1 | Q1 2018          |       20180100 |             -13 | True               |             1 |            1 | January      | Jan              | J​               | Jan 2018       |     20180100 |           -40 | True             | 2018-01-31 00:00:00.000 |  20180101 |           1 | Monday          | M​                 | Weekday    |       2018 |               -3 |                   1 | Q1            | Q1 2018              |           20180100 |                 -13 |                1 | 2018-01       |    20180100 |         -177 | True            | 2018-01-07 00:00:00.000 | FY18          |                 -3 | FQ3              |        20180300 | False         |               7 |       20180700 | False         |            28 | 2018-28              |     20182800 | False         | False          | True           | Unknown     | True            | False    | False     |
| 2018-01-02 00:00:00.000 |   2018 |           -3 | True            |               1 | Q1 2018          |       20180100 |             -13 | True               |             1 |            2 | January      | Jan              | J​               | Jan 2018       |     20180100 |           -40 | True             | 2018-01-31 00:00:00.000 |  20180102 |           2 | Tuesday         | T​​                 | Weekday    |       2018 |               -3 |                   1 | Q1            | Q1 2018              |           20180100 |                 -13 |                1 | 2018-01       |    20180100 |         -177 | True            | 2018-01-07 00:00:00.000 | FY18          |                 -3 | FQ3              |        20180300 | False         |               7 |       20180700 | False         |            28 | 2018-28              |     20182800 | False         | False          | True           | Unknown     | True            | False    | False     |
| 2018-01-03 00:00:00.000 |   2018 |           -3 | True            |               1 | Q1 2018          |       20180100 |             -13 | True               |             1 |            3 | January      | Jan              | J​               | Jan 2018       |     20180100 |           -40 | True             | 2018-01-31 00:00:00.000 |  20180103 |           3 | Wednesday       | W​​​                 | Weekday    |       2018 |               -3 |                   1 | Q1            | Q1 2018              |           20180100 |                 -13 |                1 | 2018-01       |    20180100 |         -177 | True            | 2018-01-07 00:00:00.000 | FY18          |                 -3 | FQ3              |        20180300 | False         |               7 |       20180700 | False         |            28 | 2018-28              |     20182800 | False         | False          | True           | Unknown     | True            | False    | False     |
| 2018-01-04 00:00:00.000 |   2018 |           -3 | True            |               1 | Q1 2018          |       20180100 |             -13 | True               |             1 |            4 | January      | Jan              | J​               | Jan 2018       |     20180100 |           -40 | True             | 2018-01-31 00:00:00.000 |  20180104 |           4 | Thursday        | T​​​​                 | Weekday    |       2018 |               -3 |                   1 | Q1            | Q1 2018              |           20180100 |                 -13 |                1 | 2018-01       |    20180100 |         -177 | True            | 2018-01-07 00:00:00.000 | FY18          |                 -3 | FQ3              |        20180300 | False         |               7 |       20180700 | False         |            28 | 2018-28              |     20182800 | False         | False          | True           | Unknown     | True            | False    | False     |
| 2018-01-05 00:00:00.000 |   2018 |           -3 | True            |               1 | Q1 2018          |       20180100 |             -13 | True               |             1 |            5 | January      | Jan              | J​               | Jan 2018       |     20180100 |           -40 | True             | 2018-01-31 00:00:00.000 |  20180105 |           5 | Friday          | F​​​​​                 | Weekday    |       2018 |               -3 |                   1 | Q1            | Q1 2018              |           20180100 |                 -13 |                1 | 2018-01       |    20180100 |         -177 | True            | 2018-01-07 00:00:00.000 | FY18          |                 -3 | FQ3              |        20180300 | False         |               7 |       20180700 | False         |            28 | 2018-28              |     20182800 | False         | False          | True           | Unknown     | True            | False    | False     |
| 2018-01-08 00:00:00.000 |   2018 |           -3 | True            |               1 | Q1 2018          |       20180100 |             -13 | True               |             1 |            8 | January      | Jan              | J​               | Jan 2018       |     20180100 |           -40 | True             | 2018-01-31 00:00:00.000 |  20180108 |           1 | Monday          | M​                 | Weekday    |       2018 |               -3 |                   1 | Q1            | Q1 2018              |           20180100 |                 -13 |                2 | 2018-02       |    20180200 |         -176 | True            | 2018-01-14 00:00:00.000 | FY18          |                 -3 | FQ3              |        20180300 | False         |               7 |       20180700 | False         |            29 | 2018-29              |     20182900 | False         | False          | True           | Unknown     | True            | False    | False     |
| 2018-01-09 00:00:00.000 |   2018 |           -3 | True            |               1 | Q1 2018          |       20180100 |             -13 | True               |             1 |            9 | January      | Jan              | J​               | Jan 2018       |     20180100 |           -40 | True             | 2018-01-31 00:00:00.000 |  20180109 |           2 | Tuesday         | T​​                 | Weekday    |       2018 |               -3 |                   1 | Q1            | Q1 2018              |           20180100 |                 -13 |                2 | 2018-02       |    20180200 |         -176 | True            | 2018-01-14 00:00:00.000 | FY18          |                 -3 | FQ3              |        20180300 | False         |               7 |       20180700 | False         |            29 | 2018-29              |     20182900 | False         | False          | True           | Unknown     | True            | False    | False     |
| 2018-01-10 00:00:00.000 |   2018 |           -3 | True            |               1 | Q1 2018          |       20180100 |             -13 | True               |             1 |           10 | January      | Jan              | J​               | Jan 2018       |     20180100 |           -40 | True             | 2018-01-31 00:00:00.000 |  20180110 |           3 | Wednesday       | W​​​                 | Weekday    |       2018 |               -3 |                   1 | Q1            | Q1 2018              |           20180100 |                 -13 |                2 | 2018-02       |    20180200 |         -176 | True            | 2018-01-14 00:00:00.000 | FY18          |                 -3 | FQ3              |        20180300 | False         |               7 |       20180700 | False         |            29 | 2018-29              |     20182900 | False         | False          | True           | Unknown     | True            | False    | False     |
| 2018-01-11 00:00:00.000 |   2018 |           -3 | True            |               1 | Q1 2018          |       20180100 |             -13 | True               |             1 |           11 | January      | Jan              | J​               | Jan 2018       |     20180100 |           -40 | True             | 2018-01-31 00:00:00.000 |  20180111 |           4 | Thursday        | T​​​​                 | Weekday    |       2018 |               -3 |                   1 | Q1            | Q1 2018              |           20180100 |                 -13 |                2 | 2018-02       |    20180200 |         -176 | True            | 2018-01-14 00:00:00.000 | FY18          |                 -3 | FQ3              |        20180300 | False         |               7 |       20180700 | False         |            29 | 2018-29              |     20182900 | False         | False          | True           | Unknown     | True            | False    | False     |
| 2018-01-12 00:00:00.000 |   2018 |           -3 | True            |               1 | Q1 2018          |       20180100 |             -13 | True               |             1 |           12 | January      | Jan              | J​               | Jan 2018       |     20180100 |           -40 | True             | 2018-01-31 00:00:00.000 |  20180112 |           5 | Friday          | F​​​​​                 | Weekday    |       2018 |               -3 |                   1 | Q1            | Q1 2018              |           20180100 |                 -13 |                2 | 2018-02       |    20180200 |         -176 | True            | 2018-01-14 00:00:00.000 | FY18          |                 -3 | FQ3              |        20180300 | False         |               7 |       20180700 | False         |            29 | 2018-29              |     20182900 | False         | False          | True           | Unknown     | True            | False    | False     |


Table: Locations
| Location ID   | Name            | County           | State Code   | State       | Zip Codes                                                                                                                                                                                    | Type   |   Latitude |   Longitude |   Area Code |   Population |   Households |   Median Income |   Land Area |   Water Area | Time Zone        |
|:--------------|:----------------|:-----------------|:-------------|:------------|:---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|:-------|-----------:|------------:|------------:|-------------:|-------------:|----------------:|------------:|-------------:|:-----------------|
| A100          | Bridgeport      | Fairfield County | CT           | Connecticut | 06604 06605 06606 06607 06608 06610 06611 6601 6602 6673 6699                                                                                                                                | City   |    41.167  |    -73.2048 |         203 |       147629 |        50367 |           41801 |    41591056 |      8721419 | America/New York |
| A102          | Hartford        | Hartford County  | CT           | Connecticut | 06103 06105 06106 06110 06112 06114 06117 06120 06160 6102 6104 6115 6123 6126 6132 6134 6140 6141 6142 6143 6144 6145 6146 6147 6150 6151 6153 6154 6155 6156 6160 6161 6167 6176 6180 6183 | City   |    41.7637 |    -72.6851 |         959 |       124006 |        45239 |           30630 |    45016120 |      1748078 | America/New York |
| A104          | New Haven       | New Haven County | CT           | Connecticut | 06510 06511 06512 06513 06515 06519 6501 6502 6503 6504 6505 6506 6507 6508 6509 6520 6521 6530 6531 6532 6533 6534 6535 6536 6537 6538 6540                                                 | City   |    41.3081 |    -72.9282 |         203 |       130322 |        49771 |           37192 |    48407373 |      3739487 | America/New York |
| A106          | Stamford        | Fairfield County | CT           | Connecticut | 06831 06901 06902 06903 06905 06906 06907 6904 6911 6912 6913 6914 6920 6921 6922 6925 6926 6927 6928                                                                                        | City   |    41.0534 |    -73.5387 |         203 |       128874 |        46974 |           79359 |    97430571 |     37323740 | America/New York |
| A108          | Waterbury       | New Haven County | CT           | Connecticut | 06702 06704 06705 06706 06708 06710 06770 6701 6703 6720 6721 6722 6723 6724 6725 6726 6749                                                                                                  | City   |    41.5581 |    -73.0515 |         203 |       108802 |        40213 |           40467 |    73880017 |      1086045 | America/New York |
| A111          | Cape Coral      | Lee County       | FL           | Florida     | 33903 33904 33909 33910 33914 33915 33955 33990 33991 33993                                                                                                                                  | City   |    26.5629 |    -81.9495 |         941 |       175229 |        58967 |           50536 |   273544395 |     35010453 | America/New York |
| A112          | Clearwater      | Pinellas County  | FL           | Florida     | 33755                                                                                                                                                                                        | City   |    27.979  |    -82.7666 |         727 |       113003 |        46234 |           44198 |    67027321 |     36469162 | America/New York |
| A113          | Coral Springs   | Broward County   | FL           | Florida     | 33065 33067 33071 33076                                                                                                                                                                      | City   |    26.2712 |    -80.2706 |         954 |       129485 |        40571 |           66430 |    61625913 |       520928 | America/New York |
| A115          | Fort Lauderdale | Broward County   | FL           | Florida     | 33301 33302 33303 33304 33305 33306 33307 33308 33309 33310 33311 33312 33315 33316 33318 33320 33329 33334 33335 33336 33337 33338 33339 33340 33345 33346 33348 33349 33355 33359          | City   |    26.1223 |    -80.1434 |         954 |       178590 |        73446 |           50778 |    89672308 |      4372775 | America/New York |
| A116          | Gainesville     | Alachua County   | FL           | Florida     | 32601 32602 32603 32604 32605 32606 32607 32608 32609 32610 32611 32612 32613 32614 32627 32635 32641 32653 32667                                                                            | City   |    29.6516 |    -82.3248 |         352 |       130128 |        47968 |           31818 |   161411152 |      3017660 | America/New York |


Table: Products
| Product ID   | Product Name   |   Cost |   Original Sale Price | Discount Code   |   Discount |   Current Price |   Taxes |
|:-------------|:---------------|-------:|----------------------:|:----------------|-----------:|----------------:|--------:|
| ENX2000      | Product 1      |   1367 |                  2241 |                 |          0 |            2241 |  336.15 |
| ENX2001      | Product 2      |    504 |                   951 |                 |          0 |             951 |  142.65 |
| ENX2002      | Product 3      |    534 |                   847 |                 |          0 |             847 |  127.05 |
| ENX2003      | Product 4      |   1516 |                  1783 |                 |          0 |            1783 |  267.45 |
| ENX2004      | Product 5      |    665 |                  1278 |                 |          0 |            1278 |  191.7  |
| ENX2005      | Product 6      |    561 |                   684 |                 |          0 |             684 |  102.6  |
| ENX2006      | Product 7      |   1443 |                  1826 |                 |          0 |            1826 |  273.9  |
| ENX2007      | Product 8      |   1054 |                  1405 |                 |          0 |            1405 |  210.75 |
| ENX2008      | Product 9      |    208 |                   315 |                 |          0 |             315 |   47.25 |
| ENX2009      | Product 10     |    420 |                   584 |                 |          0 |             584 |   87.6  |


Table: Sales
| Order ID   | Product ID   | Location ID   | Sales Person ID   | Customer ID   |   Quantity | Purchase Date           |   Current Price |   Line Sales |
|:-----------|:-------------|:--------------|:------------------|:--------------|-----------:|:------------------------|----------------:|-------------:|
| AX10534    | ENX2077      | A193          | EMP1017           | C1479         |          1 | 2019-03-07 00:00:00.000 |             530 |          530 |
| AX10575    | ENX2077      | A107          | EMP1018           | C1152         |          1 | 2019-05-19 00:00:00.000 |             530 |          530 |
| AX10620    | ENX2077      | A186          | EMP1016           | C1798         |          1 | 2019-09-17 00:00:00.000 |             530 |          530 |
| AX10631    | ENX2077      | A132          | EMP1019           | C1780         |          1 | 2019-02-04 00:00:00.000 |             530 |          530 |
| AX10669    | ENX2077      | A167          | EMP1036           | C1058         |          1 | 2020-10-27 00:00:00.000 |             530 |          530 |
| AX10815    | ENX2077      | A149          | EMP1002           | C1788         |          1 | 2020-09-30 00:00:00.000 |             530 |          530 |
| AX10920    | ENX2077      | A142          | EMP1000           | C1586         |          1 | 2020-01-14 00:00:00.000 |             530 |          530 |
| AX10954    | ENX2077      | A193          | EMP1012           | C1350         |          1 | 2018-03-26 00:00:00.000 |             530 |          530 |
| AX11376    | ENX2077      | A167          | EMP1031           | C1282         |          1 | 2018-11-24 00:00:00.000 |             530 |          530 |
| AX11435    | ENX2077      | A111          | EMP1038           | C1478         |          1 | 2020-06-13 00:00:00.000 |             530 |          530 |


Table: Salespersons
| ID      | Color Code   | Work Location   | Salesperson Name   |
|:--------|:-------------|:----------------|:-------------------|
| EMP1000 | #9e2ee4      | Connecticut     | Jeffrey Carter     |
| EMP1001 | #c58cae      | Hawaii          | Raymond Andrews    |
| EMP1002 | #4f430a      | Michigan        | Julia Stewart      |
| EMP1003 | #de142e      | Florida         | Robert Peterson    |
| EMP1004 | #72579c      | Idaho           | Nancy Mason        |
| EMP1005 | #b1ff42      | North Dakota    | Chris Duncan       |
| EMP1006 | #e020a2      | Alaska          | Louise Stanley     |
| EMP1007 | #eb4082      | Kansas          | Ruby Hansen        |
| EMP1008 | #9a2a54      | Nevada          | Tina Castillo      |
| EMP1009 | #cd24aa      | New Mexico      | Russell Lawrence   |


Table: Table
| Page Navigation    |
|:-------------------|
| About this Report  |
| Learning Resources |
| Scenario           |

 


tables: name: Customers
data: Customers.csv
name: Dates
data: Dates.csv
name: Locations
data: Locations.csv
name: Products
data: Products.csv
name: Sales
data: Sales.csv
name: Salespersons
data: Salespersons.csv
name: Table
data: Table.csv
label: Sales
Location ID
Locations
Location ID
Sales
Product ID
Products
Product ID
Sales
Customer ID
Customers
Customer ID
Sales
Purchase Date
Dates
Date
Sales
Sales Person ID
Salespersons
ID
output: {
    "joins": [
        {
            "from_table": "Sales",
            "from_column": "Location ID",
            "to_table": "Locations",
            "to_column": "Location ID"
        },
        {
            "from_table": "Sales",
            "from_column": "Product ID",
            "to_table": "Products",
            "to_column": "Product ID"
        },
        {
            "from_table": "Sales",
            "from_column": "Customer ID",
            "to_table": "Customers",
            "to_column": "Customer ID"
        },
        {
            "from_table": "Sales",
            "from_column": "Purchase Date",
            "to_table": "Dates",
            "to_column": "Date"
        },
        {
            "from_table": "Sales",
            "from_column": "Sales Person ID",
            "to_table": "Salespersons",
            "to_column": "ID"
        }
    ]
}