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   | BrokerFullName     | DistributionNetwork   | DistributionChannel   | 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 | Description                                                       | Active   |
|-----:|:------------------------------------------------------------------|:---------|
|    1 | Revival Request                                                   | Yes      |
|    2 | Contract Notice (Use Or Faith)                                    | Yes      |
|    3 | Accommodation Reconstruction                                      | Yes      |
|    4 | Disclaimer Of A Service                                           | Yes      |
|    5 | Automatic Representative Card Renew Without The Customer'S Renew  | No       |
|    6 | Conclusion Hellipis Service From Executive / External Cooperative | Yes      |
|    7 | Modification Request                                              | Yes      |
|    8 | Contract-Maintenance Development                                  | Yes      |
|    9 | Differences In Price Politics                                     | Yes      |
|   10 | Poor Service                                                      | Yes      |


Table: Complaints
|   ID | ComplainDate            | CompletionDate          |   CustomerID |   BrokerID |   ProductID |   ComplainPriorityID |   ComplainTypeID |   ComplainSourceID |   ComplainCategoryID |   ComplainStatusID |   AdministratorID | ClientSatisfaction   |   ExpectedReimbursement |
|-----:|:------------------------|:------------------------|-------------:|-----------:|------------:|---------------------:|-----------------:|-------------------:|---------------------:|-------------------:|------------------:|:---------------------|------------------------:|
| 8888 | 2018-01-05 00:00:00.000 | 2018-01-24 00:00:00.000 |      1814075 |      12256 |          15 |                    2 |                5 |                  5 |                   59 |                  9 |                16 | Satisfied            |                       0 |
| 8947 | 2018-01-22 00:00:00.000 | 2018-01-26 00:00:00.000 |      1845086 |      11127 |           3 |                    2 |                5 |                  5 |                   59 |                  9 |                16 | Satisfied            |                       0 |
| 8949 | 2018-01-22 00:00:00.000 | 2018-01-24 00:00:00.000 |      2463182 |      11204 |          32 |                    2 |                5 |                  5 |                   59 |                  9 |                16 | Satisfied            |                       0 |
| 8952 | 2018-01-22 00:00:00.000 | 2018-01-25 00:00:00.000 |      2348805 |       2613 |          73 |                    2 |                5 |                  5 |                   59 |                  9 |                16 | Satisfied            |                       0 |
| 8955 | 2018-01-22 00:00:00.000 | 2018-01-25 00:00:00.000 |       756561 |      11099 |           7 |                    2 |                5 |                  5 |                   59 |                  9 |                16 | Satisfied            |                       0 |
| 8958 | 2018-01-22 00:00:00.000 | 2018-01-25 00:00:00.000 |       575572 |       8932 |          40 |                    2 |                5 |                  5 |                   59 |                  9 |                16 | Satisfied            |                       0 |
| 8972 | 2018-01-23 00:00:00.000 | 2018-01-25 00:00:00.000 |      1799479 |       2178 |          66 |                    2 |                5 |                  5 |                   59 |                  9 |                16 | Satisfied            |                       0 |
| 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 |
| 9004 | 2018-01-29 00:00:00.000 | 2018-01-31 00:00:00.000 |       101467 |      11849 |          17 |                    2 |                5 |                  5 |                   59 |                  9 |                16 | Satisfied            |                       0 |
| 9009 | 2018-01-29 00:00:00.000 | 2018-01-31 00:00:00.000 |      1899906 |       2158 |          35 |                    2 |                5 |                  5 |                   59 |                  9 |                16 | Satisfied            |                       0 |


Table: Customers
|   CustomerID | LastName    | FirstName   | BirthDate               | Gender   | Type       |   RegionID | Marital Status   |   Age | Customer         | Age Group   |   AgeGroupSort |
|-------------:|:------------|:------------|:------------------------|:---------|:-----------|-----------:|:-----------------|------:|:-----------------|:------------|---------------:|
|        18283 | Mark        | Sullivan    | 1975-11-05 00:00:00.000 | Male     | Individual |        828 | Married          |    44 | Mark Sullivan    | 31-50       |              3 |
|        25484 | Johnny      | Webb        | 1976-03-21 00:00:00.000 | Male     | Individual |         24 | Married          |    44 | Johnny Webb      | 31-50       |              3 |
|        26337 | Lawrence    | Morales     | 1975-12-06 00:00:00.000 | Male     | Individual |        378 | Married          |    44 | Lawrence Morales | 31-50       |              3 |
|        36411 | Christopher | Dean        | 1975-10-09 00:00:00.000 | Male     | Individual |        106 | Married          |    44 | Christopher Dean | 31-50       |              3 |
|        36541 | Victor      | Little      | 1976-03-01 00:00:00.000 | Male     | Individual |        618 | Married          |    44 | Victor Little    | 31-50       |              3 |
|        41252 | Timothy     | Butler      | 1975-12-02 00:00:00.000 | Male     | Individual |        657 | Married          |    44 | Timothy Butler   | 31-50       |              3 |
|        45340 | Brian       | Oliver      | 1976-03-29 00:00:00.000 | Male     | Individual |        875 | Married          |    44 | Brian Oliver     | 31-50       |              3 |
|        60124 | Roy         | Ross        | 1976-08-05 00:00:00.000 | Male     | Individual |        839 | Married          |    44 | Roy Ross         | 31-50       |              3 |
|        65473 | Keith       | Kelley      | 1975-09-26 00:00:00.000 | Male     | Individual |        811 | Married          |    44 | Keith Kelley     | 31-50       |              3 |
|        96561 | Peter       | Garcia      | 1975-12-18 00:00:00.000 | Male     | Individual |        579 | Married          |    44 | Peter Garcia     | 31-50       |              3 |


Table: Date
| Date                    |   Year |   QuarterOfYear |   MonthOfYear |   DayOfMonth |   DateInt | MonthName   | MonthInCalendar   | QuarterInCalendar   |   Day of Year |   DayInWeek | DayOfWeekName   | WeekEnding              | MonthEnding             |   Week Number |   MonthnYear |   QuarternYear | Fiscal Year   | Fiscal Quarter   |   Fiscal Period | IsAfterToday   | IsWorkingDay   | IsHoliday   |   WeekOffset |   MonthOffset |   QuarterOffset |   YearOffset |   FiscalYearOffset | PYTD   | CompletedWeek   | CompletedMonth   | CompletedQuarter   | Current Month   |
|:------------------------|-------:|----------------:|--------------:|-------------:|----------:|:------------|:------------------|:--------------------|--------------:|------------:|:----------------|:------------------------|:------------------------|--------------:|-------------:|---------------:|:--------------|:-----------------|----------------:|:---------------|:---------------|:------------|-------------:|--------------:|----------------:|-------------:|-------------------:|:-------|:----------------|:-----------------|:-------------------|:----------------|
| 2011-10-03 00:00:00.000 |   2011 |               4 |            10 |            3 |  20111003 | October     | Oct 2011          | Q4 2011             |           276 |           1 | Monday          | 2011-10-09 00:00:00.000 | 2011-10-31 00:00:00.000 |            40 |     20111000 |       20110400 | FY11          | FQ4              |              10 | False          | True           | Unknown     |         -464 |          -106 |             -35 |           -9 |                 -9 | False  | True            | True             | True               | False           |
| 2011-10-10 00:00:00.000 |   2011 |               4 |            10 |           10 |  20111010 | October     | Oct 2011          | Q4 2011             |           283 |           1 | Monday          | 2011-10-16 00:00:00.000 | 2011-10-31 00:00:00.000 |            41 |     20111000 |       20110400 | FY11          | FQ4              |              10 | False          | True           | Unknown     |         -463 |          -106 |             -35 |           -9 |                 -9 | False  | True            | True             | True               | False           |
| 2011-10-17 00:00:00.000 |   2011 |               4 |            10 |           17 |  20111017 | October     | Oct 2011          | Q4 2011             |           290 |           1 | Monday          | 2011-10-23 00:00:00.000 | 2011-10-31 00:00:00.000 |            42 |     20111000 |       20110400 | FY11          | FQ4              |              10 | False          | True           | Unknown     |         -462 |          -106 |             -35 |           -9 |                 -9 | False  | True            | True             | True               | False           |
| 2011-10-24 00:00:00.000 |   2011 |               4 |            10 |           24 |  20111024 | October     | Oct 2011          | Q4 2011             |           297 |           1 | Monday          | 2011-10-30 00:00:00.000 | 2011-10-31 00:00:00.000 |            43 |     20111000 |       20110400 | FY11          | FQ4              |              10 | False          | True           | Unknown     |         -461 |          -106 |             -35 |           -9 |                 -9 | False  | True            | True             | True               | False           |
| 2011-10-31 00:00:00.000 |   2011 |               4 |            10 |           31 |  20111031 | October     | Oct 2011          | Q4 2011             |           304 |           1 | Monday          | 2011-11-06 00:00:00.000 | 2011-10-31 00:00:00.000 |            44 |     20111000 |       20110400 | FY11          | FQ4              |              10 | False          | True           | Unknown     |         -460 |          -106 |             -35 |           -9 |                 -9 | False  | True            | True             | True               | False           |
| 2011-11-07 00:00:00.000 |   2011 |               4 |            11 |            7 |  20111107 | November    | Nov 2011          | Q4 2011             |           311 |           1 | Monday          | 2011-11-13 00:00:00.000 | 2011-11-30 00:00:00.000 |            45 |     20111100 |       20110400 | FY11          | FQ4              |              11 | False          | True           | Unknown     |         -459 |          -105 |             -35 |           -9 |                 -9 | False  | True            | True             | True               | False           |
| 2011-11-14 00:00:00.000 |   2011 |               4 |            11 |           14 |  20111114 | November    | Nov 2011          | Q4 2011             |           318 |           1 | Monday          | 2011-11-20 00:00:00.000 | 2011-11-30 00:00:00.000 |            46 |     20111100 |       20110400 | FY11          | FQ4              |              11 | False          | True           | Unknown     |         -458 |          -105 |             -35 |           -9 |                 -9 | False  | True            | True             | True               | False           |
| 2011-11-21 00:00:00.000 |   2011 |               4 |            11 |           21 |  20111121 | November    | Nov 2011          | Q4 2011             |           325 |           1 | Monday          | 2011-11-27 00:00:00.000 | 2011-11-30 00:00:00.000 |            47 |     20111100 |       20110400 | FY11          | FQ4              |              11 | False          | True           | Unknown     |         -457 |          -105 |             -35 |           -9 |                 -9 | False  | True            | True             | True               | False           |
| 2011-11-28 00:00:00.000 |   2011 |               4 |            11 |           28 |  20111128 | November    | Nov 2011          | Q4 2011             |           332 |           1 | Monday          | 2011-12-04 00:00:00.000 | 2011-11-30 00:00:00.000 |            48 |     20111100 |       20110400 | FY11          | FQ4              |              11 | False          | True           | Unknown     |         -456 |          -105 |             -35 |           -9 |                 -9 | False  | True            | True             | True               | False           |
| 2011-12-05 00:00:00.000 |   2011 |               4 |            12 |            5 |  20111205 | December    | Dec 2011          | Q4 2011             |           339 |           1 | Monday          | 2011-12-11 00:00:00.000 | 2011-12-31 00:00:00.000 |            49 |     20111200 |       20110400 | FY11          | FQ4              |              12 | False          | True           | Unknown     |         -455 |          -104 |             -35 |           -9 |                 -9 | False  | True            | True             | True               | False           |


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


Table: Products
|   ProductID | ProductCategory   | ProductSubCategory   | 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           | 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 | Description         |
|-----:|:--------------------|
|    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: StateRegions
| 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: Status History
|   ID |   Complaint ID |   ComplaintStatusID | StatusDate              |   Index |   Previous Value |
|-----:|---------------:|--------------------:|:------------------------|--------:|-----------------:|
| 3750 |          10943 |                   9 | 2019-03-28 00:00:00.000 |     808 |                9 |
| 3748 |          11174 |                   9 | 2019-03-28 00:00:00.000 |    1032 |                9 |
| 3814 |          11439 |                   9 | 2019-03-28 00:00:00.000 |    1289 |                9 |
| 3809 |          11440 |                   9 | 2019-03-28 00:00:00.000 |    1290 |                9 |
| 3805 |          11441 |                   9 | 2019-03-28 00:00:00.000 |    1291 |                9 |
| 3802 |          11442 |                   9 | 2019-03-28 00:00:00.000 |    1292 |                9 |
| 3796 |          11443 |                   9 | 2019-03-28 00:00:00.000 |    1293 |                9 |
| 3794 |          11444 |                   9 | 2019-03-28 00:00:00.000 |    1294 |                9 |
| 3781 |          11445 |                   9 | 2019-03-28 00:00:00.000 |    1295 |                9 |
| 3745 |          11447 |                   9 | 2019-03-28 00:00:00.000 |    1297 |                9 |


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


Table: Types
|   ID | Description    |
|-----:|:---------------|
|    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: Complaints
data: Complaints.csv
name: Customers
data: Customers.csv
name: Date
data: Date.csv
name: Priorities
data: Priorities.csv
name: Products
data: Products.csv
name: Regions
data: Regions.csv
name: Sources
data: Sources.csv
name: StateRegions
data: StateRegions.csv
name: Status History
data: Status History.csv
name: Statuses
data: Statuses.csv
name: Types
data: Types.csv
label: Complaints
ComplainPriorityID
Priorities
ID
Complaints
ProductID
Products
ProductID
Complaints
BrokerID
Brokers
BrokerID
Complaints
ComplainStatusID
Statuses
ID
Complaints
ComplainTypeID
Types
ID
Complaints
ComplainCategoryID
Categories
ID
Complaints
ComplainSourceID
Sources
ID
Complaints
CustomerID
Customers
CustomerID
Status History
StatusDate
Date
Date
Customers
RegionID
Regions
id
Status History
Complaint ID
Complaints
ID
output: {
    "joins": [
        {
            "from_table": "Complaints",
            "from_column": "ComplainPriorityID",
            "to_table": "Priorities",
            "to_column": "ID"
        },
        {
            "from_table": "Complaints",
            "from_column": "ProductID",
            "to_table": "Products",
            "to_column": "ProductID"
        },
        {
            "from_table": "Complaints",
            "from_column": "BrokerID",
            "to_table": "Brokers",
            "to_column": "BrokerID"
        },
        {
            "from_table": "Complaints",
            "from_column": "ComplainStatusID",
            "to_table": "Statuses",
            "to_column": "ID"
        },
        {
            "from_table": "Complaints",
            "from_column": "ComplainTypeID",
            "to_table": "Types",
            "to_column": "ID"
        },
        {
            "from_table": "Complaints",
            "from_column": "ComplainCategoryID",
            "to_table": "Categories",
            "to_column": "ID"
        },
        {
            "from_table": "Complaints",
            "from_column": "ComplainSourceID",
            "to_table": "Sources",
            "to_column": "ID"
        },
        {
            "from_table": "Complaints",
            "from_column": "CustomerID",
            "to_table": "Customers",
            "to_column": "CustomerID"
        },
        {
            "from_table": "Status History",
            "from_column": "StatusDate",
            "to_table": "Date",
            "to_column": "Date"
        },
        {
            "from_table": "Customers",
            "from_column": "RegionID",
            "to_table": "Regions",
            "to_column": "id"
        },
        {
            "from_table": "Status History",
            "from_column": "Complaint ID",
            "to_table": "Complaints",
            "to_column": "ID"
        }
    ]
}