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: Brokers
|   BrokerID | BrokerCode   | Broker Name        | DistributionNetwork   | Distribution Channel   | CommissionScheme   |
|-----------:|:-------------|:-------------------|:----------------------|:-----------------------|:-------------------|
|         49 | Brk-000049   | Willie Oliver      | Direct                | Tele sales             | Comm-2             |
|        116 | Brk-000116   | Jose Mccoy         | Direct                | Tele sales             | Bonus Based        |
|        138 | Brk-000138   | Randy Alexander    | Direct                | Tele sales             | No Commission      |
|        194 | Brk-000194   | Kevin Johnson      | Direct                | Tele sales             | Comm-2             |
|        235 | Brk-000235   | Todd Gomez         | Direct                | Tele sales             | Comm-2             |
|        269 | Brk-000269   | Howard Wright      | Direct                | Tele sales             | Comm-7             |
|        347 | Brk-000347   | Harold Weaver      | Direct                | Tele sales             | Comm-2             |
|        356 | Brk-000356   | Matthew Scott      | Direct                | Tele sales             | No Commission      |
|        486 | Brk-000486   | Victor Robertson   | Direct                | Tele sales             | No Commission      |
|        597 | Brk-000597   | Jeffrey Williamson | Direct                | Tele sales             | Comm-6             |


Table: Categories
|   ID | Category                                                          |   Active |
|-----:|:------------------------------------------------------------------|---------:|
|    1 | Revival Request                                                   |        1 |
|    2 | Contract Notice (Use Or Faith)                                    |        1 |
|    3 | Accommodation Reconstruction                                      |        1 |
|    4 | Disclaimer Of A Service                                           |        1 |
|    5 | Automatic Representative Card Renew Without The Customer'S Renew  |        0 |
|    6 | Conclusion Hellipis Service From Executive / External Cooperative |        1 |
|    7 | Modification Request                                              |        1 |
|    8 | Contract-Maintenance Development                                  |        1 |
|    9 | Differences In Price Politics                                     |        1 |
|   10 | Poor Service                                                      |        1 |


Table: Complaint Status History
|   ID |   ComplaintID |   ComplaintStatusID | StatusDate              |
|-----:|--------------:|--------------------:|:------------------------|
| 3750 |         10943 |                   9 | 2019-03-28 00:00:00.000 |
| 3748 |         11174 |                   9 | 2019-03-28 00:00:00.000 |
| 3814 |         11439 |                   9 | 2019-03-28 00:00:00.000 |
| 3809 |         11440 |                   9 | 2019-03-28 00:00:00.000 |
| 3805 |         11441 |                   9 | 2019-03-28 00:00:00.000 |
| 3802 |         11442 |                   9 | 2019-03-28 00:00:00.000 |
| 3796 |         11443 |                   9 | 2019-03-28 00:00:00.000 |
| 3794 |         11444 |                   9 | 2019-03-28 00:00:00.000 |
| 3781 |         11445 |                   9 | 2019-03-28 00:00:00.000 |
| 3745 |         11447 |                   9 | 2019-03-28 00:00:00.000 |


Table: Complaints Data
|   ID | Complain Date           | Completion Date         |   CustomerID |   BrokerID |   ProductID |   ComplainPriorityID |   ComplainTypeID |   ComplainSourceID |   ComplainCategoryID |   ComplainStatusID |   AdministratorID | Satisfaction Level   |   ExpectedReimbursement |   Days To Resolution |   Days Since Complaint |
|-----:|:------------------------|:------------------------|-------------:|-----------:|------------:|---------------------:|-----------------:|-------------------:|---------------------:|-------------------:|------------------:|:---------------------|------------------------:|---------------------:|-----------------------:|
| 8986 | 2018-01-25 00:00:00.000 | 2018-01-26 00:00:00.000 |      2084052 |      11217 |          40 |                    2 |                5 |                  5 |                   59 |                  9 |                16 | Satisfied            |                       0 |                    1 |                   1384 |
| 9103 | 2018-02-15 00:00:00.000 | 2018-02-16 00:00:00.000 |      1800346 |       8367 |          33 |                    2 |                5 |                  5 |                   59 |                  9 |                16 | Satisfied            |                       0 |                    1 |                   1363 |
| 9122 | 2018-02-20 00:00:00.000 | 2018-02-21 00:00:00.000 |       323379 |      11487 |          29 |                    2 |                5 |                  5 |                   59 |                  9 |                16 | Satisfied            |                       0 |                    1 |                   1358 |
| 9141 | 2018-02-26 00:00:00.000 | 2018-02-27 00:00:00.000 |      2221733 |       9670 |          24 |                    2 |                5 |                  5 |                   59 |                  9 |                16 | Satisfied            |                       0 |                    1 |                   1352 |
| 9206 | 2018-03-08 00:00:00.000 | 2018-03-09 00:00:00.000 |      1750178 |        116 |          76 |                    2 |                5 |                  5 |                   59 |                  9 |                16 | Satisfied            |                       0 |                    1 |                   1342 |
| 9209 | 2018-03-08 00:00:00.000 | 2018-03-09 00:00:00.000 |       626565 |       9797 |           2 |                    2 |                5 |                  5 |                   59 |                  9 |                16 | Satisfied            |                       0 |                    1 |                   1342 |
| 9211 | 2018-03-08 00:00:00.000 | 2018-03-09 00:00:00.000 |       166640 |      12617 |          66 |                    2 |                5 |                  5 |                   59 |                  9 |                16 | Satisfied            |                       0 |                    1 |                   1342 |
| 9212 | 2018-03-08 00:00:00.000 | 2018-03-09 00:00:00.000 |       437242 |       1866 |          71 |                    2 |                5 |                  5 |                   59 |                  9 |                16 | Satisfied            |                       0 |                    1 |                   1342 |
| 9263 | 2018-03-22 00:00:00.000 | 2018-03-23 00:00:00.000 |        60124 |      12229 |          66 |                    2 |                5 |                  5 |                   59 |                  9 |                16 | Satisfied            |                       0 |                    1 |                   1328 |
| 9264 | 2018-03-22 00:00:00.000 | 2018-03-23 00:00:00.000 |       395388 |      11066 |          17 |                    2 |                5 |                  5 |                   59 |                  9 |                16 | Satisfied            |                       0 |                    1 |                   1328 |


Table: Customers
|   CustomerID | Last Name   | First Name   | Birth Date              | Gender   | Customer Type   |   RegionID | Marital Status   |   Age |
|-------------:|:------------|:-------------|:------------------------|:---------|:----------------|-----------:|:-----------------|------:|
|          110 | Thomas      | Burke        | 1971-09-04 00:00:00.000 | Male     | Individual      |        388 | Married          |    51 |
|         1240 | Scott       | Roberts      | 1978-01-31 00:00:00.000 | Male     | Individual      |        863 | Married          |    44 |
|         1475 | Gerald      | Rogers       | 2012-06-25 00:00:00.000 | Male     | Individual      |        627 | Married          |    10 |
|         1517 | Anthony     | Knight       | 1969-03-19 00:00:00.000 | Male     | Individual      |        840 | Married          |    53 |
|         1582 | Scott       | Miller       | 1967-07-12 00:00:00.000 | Male     | Individual      |        142 | Married          |    55 |
|         2395 | Charles     | Riley        | 1969-10-25 00:00:00.000 | Male     | Individual      |        983 | Married          |    53 |
|         2932 | Jesse       | Lane         | 1991-07-17 00:00:00.000 | Male     | Individual      |        837 | Married          |    31 |
|         4109 | Jack        | Simmons      | 1960-12-10 00:00:00.000 | Male     | Individual      |         59 | Married          |    62 |
|         4437 | Larry       | Garza        | 1973-07-21 00:00:00.000 | Male     | Individual      |         16 | Married          |    49 |
|         5464 | Russell     | Harper       | 1963-03-17 00:00:00.000 | Male     | Individual      |        385 | Married          |    59 |


Table: Dates
| Date                    |   Year |   QuarterOfYear |   MonthOfYear |   DayOfMonth |   DateInt | Month Name   | Month & Year   | Quarter & Year   |   DayOfWeek | DayOfWeekName   | WeekEnding              | MonthEnding             |   ISO Weeknumber |   ISO Year |   ISO Day of Year | Week & Year   |   WeeknYear |   MonthnYear |   QuarternYear | Fiscal Year   | Fiscal Quarter   |   Fiscal Period | IsAfterToday   | IsWorkingDay   | IsHoliday   | IsBusinessDay   | Day Type   |   WeekOffset |   MonthOffset |   QuarterOffset |   YearOffset |   FiscalYearOffset | WeekCompleted   | MonthCompleted   | QuarterCompleted   | Previous Two Years   |
|:------------------------|-------:|----------------:|--------------:|-------------:|----------:|:-------------|:---------------|:-----------------|------------:|:----------------|:------------------------|:------------------------|-----------------:|-----------:|------------------:|:--------------|------------:|-------------:|---------------:|:--------------|:-----------------|----------------:|:---------------|:---------------|:------------|:----------------|:-----------|-------------:|--------------:|----------------:|-------------:|-------------------:|:----------------|:-----------------|:-------------------|:---------------------|
| 2020-01-01 00:00:00.000 |   2020 |               1 |             1 |            1 |  20200101 | January      | Jan 2020       | Q1 2020          |           3 | Wednesday       | 2020-01-05 00:00:00.000 | 2020-01-31 00:00:00.000 |                1 |       2020 |                 3 | 2020-01       |    20200100 |     20200100 |       20200100 | FY20          | FQ3              |               7 | False          | True           | Unknown     | True            | Weekday    |          -97 |           -22 |              -7 |           -1 |                 -2 | True            | True             | True               | True                 |
| 2020-01-02 00:00:00.000 |   2020 |               1 |             1 |            2 |  20200102 | January      | Jan 2020       | Q1 2020          |           4 | Thursday        | 2020-01-05 00:00:00.000 | 2020-01-31 00:00:00.000 |                1 |       2020 |                 4 | 2020-01       |    20200100 |     20200100 |       20200100 | FY20          | FQ3              |               7 | False          | True           | Unknown     | True            | Weekday    |          -97 |           -22 |              -7 |           -1 |                 -2 | True            | True             | True               | True                 |
| 2020-01-03 00:00:00.000 |   2020 |               1 |             1 |            3 |  20200103 | January      | Jan 2020       | Q1 2020          |           5 | Friday          | 2020-01-05 00:00:00.000 | 2020-01-31 00:00:00.000 |                1 |       2020 |                 5 | 2020-01       |    20200100 |     20200100 |       20200100 | FY20          | FQ3              |               7 | False          | True           | Unknown     | True            | Weekday    |          -97 |           -22 |              -7 |           -1 |                 -2 | True            | True             | True               | True                 |
| 2020-01-06 00:00:00.000 |   2020 |               1 |             1 |            6 |  20200106 | January      | Jan 2020       | Q1 2020          |           1 | Monday          | 2020-01-12 00:00:00.000 | 2020-01-31 00:00:00.000 |                2 |       2020 |                 8 | 2020-02       |    20200200 |     20200100 |       20200100 | FY20          | FQ3              |               7 | False          | True           | Unknown     | True            | Weekday    |          -96 |           -22 |              -7 |           -1 |                 -2 | True            | True             | True               | True                 |
| 2020-01-07 00:00:00.000 |   2020 |               1 |             1 |            7 |  20200107 | January      | Jan 2020       | Q1 2020          |           2 | Tuesday         | 2020-01-12 00:00:00.000 | 2020-01-31 00:00:00.000 |                2 |       2020 |                 9 | 2020-02       |    20200200 |     20200100 |       20200100 | FY20          | FQ3              |               7 | False          | True           | Unknown     | True            | Weekday    |          -96 |           -22 |              -7 |           -1 |                 -2 | True            | True             | True               | True                 |
| 2020-01-08 00:00:00.000 |   2020 |               1 |             1 |            8 |  20200108 | January      | Jan 2020       | Q1 2020          |           3 | Wednesday       | 2020-01-12 00:00:00.000 | 2020-01-31 00:00:00.000 |                2 |       2020 |                10 | 2020-02       |    20200200 |     20200100 |       20200100 | FY20          | FQ3              |               7 | False          | True           | Unknown     | True            | Weekday    |          -96 |           -22 |              -7 |           -1 |                 -2 | True            | True             | True               | True                 |
| 2020-01-09 00:00:00.000 |   2020 |               1 |             1 |            9 |  20200109 | January      | Jan 2020       | Q1 2020          |           4 | Thursday        | 2020-01-12 00:00:00.000 | 2020-01-31 00:00:00.000 |                2 |       2020 |                11 | 2020-02       |    20200200 |     20200100 |       20200100 | FY20          | FQ3              |               7 | False          | True           | Unknown     | True            | Weekday    |          -96 |           -22 |              -7 |           -1 |                 -2 | True            | True             | True               | True                 |
| 2020-01-10 00:00:00.000 |   2020 |               1 |             1 |           10 |  20200110 | January      | Jan 2020       | Q1 2020          |           5 | Friday          | 2020-01-12 00:00:00.000 | 2020-01-31 00:00:00.000 |                2 |       2020 |                12 | 2020-02       |    20200200 |     20200100 |       20200100 | FY20          | FQ3              |               7 | False          | True           | Unknown     | True            | Weekday    |          -96 |           -22 |              -7 |           -1 |                 -2 | True            | True             | True               | True                 |
| 2020-01-13 00:00:00.000 |   2020 |               1 |             1 |           13 |  20200113 | January      | Jan 2020       | Q1 2020          |           1 | Monday          | 2020-01-19 00:00:00.000 | 2020-01-31 00:00:00.000 |                3 |       2020 |                15 | 2020-03       |    20200300 |     20200100 |       20200100 | FY20          | FQ3              |               7 | False          | True           | Unknown     | True            | Weekday    |          -95 |           -22 |              -7 |           -1 |                 -2 | True            | True             | True               | True                 |
| 2020-01-14 00:00:00.000 |   2020 |               1 |             1 |           14 |  20200114 | January      | Jan 2020       | Q1 2020          |           2 | Tuesday         | 2020-01-19 00:00:00.000 | 2020-01-31 00:00:00.000 |                3 |       2020 |                16 | 2020-03       |    20200300 |     20200100 |       20200100 | FY20          | FQ3              |               7 | False          | True           | Unknown     | True            | Weekday    |          -95 |           -22 |              -7 |           -1 |                 -2 | True            | True             | True               | True                 |


Table: Priorities
|   ID | Priority   |
|-----:|:-----------|
|    1 | Urgent     |
|    2 | Non Urgent |


Table: Products
|   ProductID | Product Category   | Product Sub Category   | Product   |
|------------:|:-------------------|:-----------------------|:----------|
|           1 | Property           | Non Mortgaged          | PNM-001   |
|           2 | Property           | Non Mortgaged          | PNM-002   |
|           3 | Property           | Non Mortgaged          | PNM-003   |
|           4 | Mortgaged          | Mortgaged Property     | MMP-004   |
|           5 | Mortgaged          | Mortgaged Property     | MMP-005   |
|           6 | Mortgaged          | Mortgaged Property     | MMP-006   |
|           7 | Motor              | Motor Standard         | MOT-007   |
|           8 | Motor              | Motor Plus             | MOT-008   |
|           9 | Motor              | Motor Standard         | MOT-009   |
|          10 | Motor              | Motor Plus             | MOT-010   |


Table: Regions
|   id | Name         | County              | State Code   | State      | Type   |   Latitude |   Longitude |   Area code |   Population |   Households |   Median income |   Land Area |   Water Area | Time Zone           | State Region   |
|-----:|:-------------|:--------------------|:-------------|:-----------|:-------|-----------:|------------:|------------:|-------------:|-------------:|----------------:|------------:|-------------:|:--------------------|:---------------|
|   38 | Alameda      | Alameda County      | CA           | California | City   |    37.7652 |    -122.242 |         510 |        78630 |        30708 |           79312 |    27045458 |     32778370 | America/Los Angeles | West           |
|   39 | Alhambra     | Los Angeles County  | CA           | California | City   |    34.0953 |    -118.127 |         626 |        85551 |        29174 |           53582 |    19764230 |         1730 | America/Los Angeles | West           |
|   40 | Aliso Viejo  | Orange County       | CA           | California | City   |    33.565  |    -117.727 |         949 |        50195 |        18425 |          102367 |    17921214 |            0 | America/Los Angeles | West           |
|   41 | Anaheim      | Orange County       | CA           | California | City   |    33.8353 |    -117.915 |         657 |       350742 |        99670 |           60752 |   129438076 |      2457142 | America/Los Angeles | West           |
|   42 | Antioch      | Contra Costa County | CA           | California | City   |    38.0049 |    -121.806 |         925 |       110542 |        33718 |           64329 |    76173197 |      1842711 | America/Los Angeles | West           |
|   44 | Arcadia      | Los Angeles County  | CA           | California | City   |    34.1397 |    -118.035 |         626 |        58408 |        19559 |           79934 |    28295893 |       540014 | America/Los Angeles | West           |
|   46 | Bakersfield  | Kern County         | CA           | California | City   |    35.3733 |    -119.019 |         661 |       373640 |       112439 |           57095 |   385366784 |      3809676 | America/Los Angeles | West           |
|   47 | Baldwin Park | Los Angeles County  | CA           | California | City   |    34.0853 |    -117.961 |         626 |        77071 |        17674 |           51742 |    17174857 |       399817 | America/Los Angeles | West           |
|   48 | Bellflower   | Los Angeles County  | CA           | California | City   |    33.8817 |    -118.117 |         562 |        78441 |        23176 |           48823 |    15845460 |       138188 | America/Los Angeles | West           |
|   49 | Berkeley     | Alameda County      | CA           | California | City   |    37.8716 |    -122.273 |         510 |       120972 |        45917 |           66237 |    27106077 |     18715614 | America/Los Angeles | West           |


Table: Sources
|   ID | Sources             |
|-----:|:--------------------|
|    1 | Bank Branch         |
|    2 | Tele                |
|    3 | Bank HQ             |
|    4 | Partner             |
|    5 | Company HQ          |
|    6 | Intermediary        |
|    7 | CRO                 |
|    8 | Call Center         |
|    9 | Bank Small Business |


Table: State Regions
| State Code   | State                | Region    |
|:-------------|:---------------------|:----------|
| AL           | Alabama              | South     |
| AR           | Arkansas             | South     |
| AZ           | Arizona              | West      |
| CA           | California           | West      |
| CO           | Colorado             | West      |
| CT           | Connecticut          | Northeast |
| DC           | District of Columbia | South     |
| DE           | Delaware             | South     |
| FL           | Florida              | South     |
| GA           | Georgia              | South     |


Table: Statuses
|   ID | Status      |
|-----:|:------------|
|    1 | Outstanding |
|    2 | New         |
|    3 | In Progress |
|    4 | Assigned To |
|    5 | Resolved    |
|    6 | Pending     |
|    9 | Closed      |


Table: Types
|   ID | Channel Type   |
|-----:|:---------------|
|    1 | Court          |
|    2 | In person      |
|    3 | Written        |
|    4 | Court Order    |
|    5 | Email          |
|    6 | Fax            |
|    7 | Phone          |
|    8 | Call center    |
|    9 | Partner / Bank |
|   99 | Other          |

 


tables: name: Brokers
data: Brokers.csv
name: Categories
data: Categories.csv
name: Complaint Status History
data: Complaint Status History.csv
name: Complaints Data
data: Complaints Data.csv
name: Customers
data: Customers.csv
name: Dates
data: Dates.csv
name: Priorities
data: Priorities.csv
name: Products
data: Products.csv
name: Regions
data: Regions.csv
name: Sources
data: Sources.csv
name: State Regions
data: State Regions.csv
name: Statuses
data: Statuses.csv
name: Types
data: Types.csv
label: Complaints Data
ProductID
Products
ProductID
Complaints Data
CustomerID
Customers
CustomerID
Complaints Data
BrokerID
Brokers
BrokerID
Complaints Data
Complain Date
Dates
Date
Customers
RegionID
Regions
id
Complaints Data
ComplainCategoryID
Categories
ID
Complaints Data
ComplainStatusID
Statuses
ID
Complaints Data
ComplainPriorityID
Priorities
ID
Complaints Data
ComplainSourceID
Sources
ID
Complaints Data
ComplainTypeID
Types
ID
Regions
State Code
State Regions
State Code
Complaint Status History
ComplaintID
Complaints Data
ID
output: {
    "joins": [
        {
            "from_table": "Complaints Data",
            "from_column": "ProductID",
            "to_table": "Products",
            "to_column": "ProductID"
        },
        {
            "from_table": "Complaints Data",
            "from_column": "CustomerID",
            "to_table": "Customers",
            "to_column": "CustomerID"
        },
        {
            "from_table": "Complaints Data",
            "from_column": "BrokerID",
            "to_table": "Brokers",
            "to_column": "BrokerID"
        },
        {
            "from_table": "Complaints Data",
            "from_column": "Complain Date",
            "to_table": "Dates",
            "to_column": "Date"
        },
        {
            "from_table": "Customers",
            "from_column": "RegionID",
            "to_table": "Regions",
            "to_column": "id"
        },
        {
            "from_table": "Complaints Data",
            "from_column": "ComplainCategoryID",
            "to_table": "Categories",
            "to_column": "ID"
        },
        {
            "from_table": "Complaints Data",
            "from_column": "ComplainStatusID",
            "to_table": "Statuses",
            "to_column": "ID"
        },
        {
            "from_table": "Complaints Data",
            "from_column": "ComplainPriorityID",
            "to_table": "Priorities",
            "to_column": "ID"
        },
        {
            "from_table": "Complaints Data",
            "from_column": "ComplainSourceID",
            "to_table": "Sources",
            "to_column": "ID"
        },
        {
            "from_table": "Complaints Data",
            "from_column": "ComplainTypeID",
            "to_table": "Types",
            "to_column": "ID"
        },
        {
            "from_table": "Regions",
            "from_column": "State Code",
            "to_table": "State Regions",
            "to_column": "State Code"
        },
        {
            "from_table": "Complaint Status History",
            "from_column": "ComplaintID",
            "to_table": "Complaints Data",
            "to_column": "ID"
        }
    ]
}