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: Agreement Dates
| Date                    |   Year |   QuarterOfYear |   MonthOfYear |   DayOfMonth |   DateInt | MonthName   | MonthInCalendar   | QuarterInCalendar   |   DayInWeek | DayOfWeekName   | WeekEnding              |   Week Number |   MonthnYear |   QuarternYear |   ShortYear | FY   |
|:------------------------|-------:|----------------:|--------------:|-------------:|----------:|:------------|:------------------|:--------------------|------------:|:----------------|:------------------------|--------------:|-------------:|---------------:|------------:|:-----|
| 2018-07-01 00:00:00.000 |   2018 |               3 |             7 |            1 |  20180701 | July        | Jul 2018          | Q3 2018             |           6 | Sunday          | 2018-07-01 00:00:00.000 |            26 |     20180700 |       20180300 |          18 | FY19 |
| 2018-07-02 00:00:00.000 |   2018 |               3 |             7 |            2 |  20180702 | July        | Jul 2018          | Q3 2018             |           0 | Monday          | 2018-07-08 00:00:00.000 |            27 |     20180700 |       20180300 |          18 | FY19 |
| 2018-07-03 00:00:00.000 |   2018 |               3 |             7 |            3 |  20180703 | July        | Jul 2018          | Q3 2018             |           1 | Tuesday         | 2018-07-08 00:00:00.000 |            27 |     20180700 |       20180300 |          18 | FY19 |
| 2018-07-04 00:00:00.000 |   2018 |               3 |             7 |            4 |  20180704 | July        | Jul 2018          | Q3 2018             |           2 | Wednesday       | 2018-07-08 00:00:00.000 |            27 |     20180700 |       20180300 |          18 | FY19 |
| 2018-07-05 00:00:00.000 |   2018 |               3 |             7 |            5 |  20180705 | July        | Jul 2018          | Q3 2018             |           3 | Thursday        | 2018-07-08 00:00:00.000 |            27 |     20180700 |       20180300 |          18 | FY19 |
| 2018-07-06 00:00:00.000 |   2018 |               3 |             7 |            6 |  20180706 | July        | Jul 2018          | Q3 2018             |           4 | Friday          | 2018-07-08 00:00:00.000 |            27 |     20180700 |       20180300 |          18 | FY19 |
| 2018-07-07 00:00:00.000 |   2018 |               3 |             7 |            7 |  20180707 | July        | Jul 2018          | Q3 2018             |           5 | Saturday        | 2018-07-08 00:00:00.000 |            27 |     20180700 |       20180300 |          18 | FY19 |
| 2018-07-08 00:00:00.000 |   2018 |               3 |             7 |            8 |  20180708 | July        | Jul 2018          | Q3 2018             |           6 | Sunday          | 2018-07-08 00:00:00.000 |            27 |     20180700 |       20180300 |          18 | FY19 |
| 2018-07-09 00:00:00.000 |   2018 |               3 |             7 |            9 |  20180709 | July        | Jul 2018          | Q3 2018             |           0 | Monday          | 2018-07-15 00:00:00.000 |            28 |     20180700 |       20180300 |          18 | FY19 |
| 2018-07-10 00:00:00.000 |   2018 |               3 |             7 |           10 |  20180710 | July        | Jul 2018          | Q3 2018             |           1 | Tuesday         | 2018-07-15 00:00:00.000 |            28 |     20180700 |       20180300 |          18 | FY19 |


Table: Agreement
|     Id |   SiteId |   MaterialTypeId |   ContainerTypeId |   SalesRepId |   number | AgreementDate           | StartDate               | EndDate   | ChargeMinimalAmount   | MinimumTonnes   | WeightAllowance   |
|-------:|---------:|-----------------:|------------------:|-------------:|---------:|:------------------------|:------------------------|:----------|:----------------------|:----------------|:------------------|
| 212404 |   171902 |              303 |               303 |          809 |    20789 | 2019-01-04 00:00:00.000 | 2019-01-04 00:00:00.000 | NULL      | True                  | NULL            | NULL              |
| 212908 |   172407 |              303 |               303 |          809 |    20796 | 2019-01-07 00:00:00.000 | 2019-01-10 00:00:00.000 | NULL      | True                  | NULL            | NULL              |
| 214829 |   174126 |              303 |               303 |          809 |    20850 | 2019-01-16 00:00:00.000 | 2019-01-16 00:00:00.000 | NULL      | True                  | NULL            | NULL              |
| 215333 |   174428 |              303 |               303 |          809 |    20861 | 2019-01-16 00:00:00.000 | 2019-01-16 00:00:00.000 | NULL      | True                  | NULL            | NULL              |
| 215334 |   174429 |              303 |               303 |          809 |    20864 | 2019-01-16 00:00:00.000 | 2019-01-16 00:00:00.000 | NULL      | True                  | NULL            | NULL              |
| 215335 |   174430 |              303 |               303 |          809 |    20866 | 2019-01-17 00:00:00.000 | 2019-01-17 00:00:00.000 | NULL      | True                  | NULL            | NULL              |
| 247955 |   174831 |              303 |               303 |          809 |    21941 | 2019-07-04 00:00:00.000 | 2019-07-04 00:00:00.000 | NULL      | True                  | NULL            | NULL              |
| 217049 |   175841 |              303 |               303 |          809 |    20913 | 2019-01-23 00:00:00.000 | 2019-01-30 00:00:00.000 | NULL      | True                  | NULL            | NULL              |
| 217052 |   175842 |              303 |               303 |          809 |    20916 | 2019-01-24 00:00:00.000 | 2019-01-28 00:00:00.000 | NULL      | True                  | NULL            | NULL              |
| 218362 |   177053 |              303 |               303 |          809 |    20942 | 2019-01-28 00:00:00.000 | 2019-02-05 00:00:00.000 | NULL      | True                  | NULL            | NULL              |


Table: ContainerGroups
|   id | Container Group   |
|-----:|:------------------|
|  202 | Wheeled Bins      |
|  303 | Bags              |
|  304 | Other             |
|  305 | Roro              |
|  306 | Rel               |
|  307 | Fel               |
|  308 | Other             |
|  404 | DUTY              |
|  606 | Old Unused        |


Table: ContainerTypes
|   Id |   ContainerGroupId | Container Type   |   Capacity |
|-----:|-------------------:|:-----------------|-----------:|
|  303 |                202 | 1100L            |       1100 |
|  304 |                202 | 240L             |        240 |
|  305 |                303 | BAGSPP           |         80 |
|  306 |                202 | 360L             |        360 |
|  307 |                202 | 660L             |        660 |
|  308 |                305 | COR40YD          |      30582 |
|  309 |                307 | FEL8YD           |       6116 |
|  310 |                305 | OPR40YD          |      30582 |
|  311 |                202 | 820L             |        820 |
|  312 |                304 | OPS8YD           |       6116 |


Table: ContractPrice
|    Id |   TypeId |   AgreementId |
|------:|---------:|--------------:|
| 15453 |        3 |        212201 |
| 15454 |        3 |        212202 |
| 15455 |        3 |        212205 |
| 15456 |        3 |        212206 |
| 15554 |        3 |        212403 |
| 15555 |        3 |        212404 |
| 15655 |        3 |        212504 |
| 15656 |        3 |        212505 |
| 15958 |        3 |        213009 |
| 16059 |        3 |        213110 |


Table: ContractPricePeriod
|    Id |   ContractPriceId | ValidFrom               | ValidTo   |   Price |
|------:|------------------:|:------------------------|:----------|--------:|
| 17069 |             15554 | 2019-01-08 00:00:00.000 | NULL      |     0.1 |
| 17070 |             15555 | 2019-01-07 00:00:00.000 | NULL      |     0.1 |
| 17170 |             15655 | 2019-01-09 00:00:00.000 | NULL      |     0.1 |
| 17372 |             15857 | 2019-01-09 00:00:00.000 | NULL      |     0.1 |
| 17473 |             15958 | 2019-01-11 00:00:00.000 | NULL      |     0.1 |
| 17574 |             16059 | 2019-01-11 00:00:00.000 | NULL      |     0.1 |
| 17675 |             16160 | 2019-01-10 00:00:00.000 | NULL      |     0.1 |
| 17677 |             16162 | 2019-01-14 00:00:00.000 | NULL      |     0.1 |
| 17978 |             16463 | 2019-01-14 00:00:00.000 | NULL      |     0.1 |
| 18079 |             16564 | 2019-01-11 00:00:00.000 | NULL      |     0.1 |


Table: Customer
|    Id | Name       | AccountCode   |   CustomerTypeId |
|------:|:-----------|:--------------|-----------------:|
| 15972 | Company 1  | 1STE0227      |                0 |
| 17597 | Company 5  | KENN3155      |                0 |
| 17641 | Company 6  | KPWA3484      |                0 |
| 18304 | Company 7  | MANORBUI      |                0 |
| 26566 | Company 9  | EVEN4532      |                0 |
| 30704 | Company 10 | WHOL5142      |                0 |
| 51208 | Company 11 | WHOL1658      |                0 |
| 51612 | Company 12 | WIL51226      |                0 |
| 56156 | Company 13 | UPTO1558      |                0 |
| 56965 | Company 14 | MOBI3889      |                0 |


Table: CustomerType
|   Id | Name   | IsBroker   |
|-----:|:-------|:-----------|
|    0 | Direct | False      |
|    1 | Broker | True       |


Table: IndustryTypes
|   Id | Industry Type           |
|-----:|:------------------------|
|    1 | Default                 |
|   62 | Shopping Centre         |
|   64 | Supermarket             |
|   65 | Superstore              |
|   67 | Departmental Store      |
|   68 | Store / Retail Unit     |
|   69 | Minimarket              |
|   70 | Newsagent / Off-licence |
|   71 | Hairdresser             |
|   72 | Outdoor / Stalls Market |


Table: Job
|      Id |   AgreementId | RecordCreationDate      | Date                    |   ActualQuantity |   Weight |   Volume |   Job Volume |
|--------:|--------------:|:------------------------|:------------------------|-----------------:|---------:|---------:|-------------:|
| 2198488 |        213215 | 2019-01-10 00:00:00.000 | 2019-01-17 00:00:00.000 |                1 |       88 |     1100 |         1100 |
| 2198491 |        213215 | 2019-01-10 00:00:00.000 | 2019-02-07 00:00:00.000 |                1 |       32 |     1100 |         1100 |
| 2201625 |        212909 | 2019-01-11 00:00:00.000 | 2019-02-08 00:00:00.000 |                1 |       64 |     1100 |         1100 |
| 2211238 |        213215 | 2019-01-17 00:00:00.000 | 2019-02-14 00:00:00.000 |                1 |       64 |     1100 |         1100 |
| 2211312 |        215135 | 2019-01-17 00:00:00.000 | 2019-01-28 00:00:00.000 |                1 |      102 |     1100 |         1100 |
| 2211314 |        215135 | 2019-01-17 00:00:00.000 | 2019-02-11 00:00:00.000 |                1 |      140 |     1100 |         1100 |
| 2213222 |        212909 | 2019-01-18 00:00:00.000 | 2019-02-15 00:00:00.000 |                1 |       18 |     1100 |         1100 |
| 2215690 |        215135 | 2019-01-21 00:00:00.000 | 2019-02-18 00:00:00.000 |                1 |      106 |     1100 |         1100 |
| 2222711 |        217050 | 2019-01-24 00:00:00.000 | 2019-02-12 00:00:00.000 |                1 |       54 |     1100 |         1100 |
| 2228745 |        217050 | 2019-01-29 00:00:00.000 | 2019-02-26 00:00:00.000 |                1 |       46 |     1100 |         1100 |


Table: Key Measures
|   Column1 |
|----------:|
|         1 |


Table: MaterialTypes
|   Id | Material Type   |
|-----:|:----------------|
|  303 | Trade Waste     |
|  304 | Cardboard       |
|  305 | Mixed Recyclate |
|  306 | Glass           |
|  307 | Scrap Metal     |
|  308 | Wood            |
|  309 | Paper           |
|  310 | Plastic         |
|  311 | Food Waste      |
|  312 | Hardcore        |


Table: Price
|     id |   Type | FromDate                | ToDate   |   AgreementId |   PriceBand1_Amount |
|-------:|-------:|:------------------------|:---------|--------------:|--------------------:|
| 297651 |     16 | 2019-01-03 00:00:00.000 |          |        212202 |                  10 |
| 297662 |     16 | 2019-01-03 00:00:00.000 |          |        212205 |                  10 |
| 297665 |     16 | 2019-01-03 00:00:00.000 |          |        212206 |                  10 |
| 297852 |     16 | 2019-01-04 00:00:00.000 |          |        212504 |                  10 |
| 298255 |     16 | 2019-01-07 00:00:00.000 |          |        212605 |                  10 |
| 298459 |     16 | 2019-01-08 00:00:00.000 |          |        212808 |                  10 |
| 298559 |     16 | 2019-01-07 00:00:00.000 |          |        212908 |                  10 |
| 298564 |     16 | 2019-01-07 00:00:00.000 |          |        212910 |                  10 |
| 298761 |     16 | 2019-01-08 00:00:00.000 |          |        213009 |                  10 |
| 299068 |     16 | 2019-01-10 00:00:00.000 |          |        213212 |                  10 |


Table: ServiceSchedule
|      Id |   AgreementId |   ST0_EveryWeeks | ValidFrom               | ValidTo   |
|--------:|--------------:|-----------------:|:------------------------|:----------|
| 1137967 |        211999 |                1 | 2019-01-02 00:00:00.000 |           |
| 1138474 |        212202 |                1 | 2019-01-12 00:00:00.000 |           |
| 1138475 |        212203 |                1 | 2019-01-12 00:00:00.000 |           |
| 1138478 |        212205 |                1 | 2019-01-10 00:00:00.000 |           |
| 1138479 |        212206 |                1 | 2019-01-10 00:00:00.000 |           |
| 1138876 |        212204 |                1 | 2019-01-05 00:00:00.000 |           |
| 1139079 |        212505 |                1 | 2019-01-05 00:00:00.000 |           |
| 1139584 |        212504 |                1 | 2019-01-16 00:00:00.000 |           |
| 1139585 |        212808 |                1 | 2019-01-11 00:00:00.000 |           |
| 1139686 |        212909 |                1 | 2019-01-10 00:00:00.000 |           |


Table: ServiceScheduleEntry
|     id |   ServiceScheduleId |   Quantity |
|-------:|--------------------:|-----------:|
| 318655 |         1.13797e+06 |          1 |
| 318656 |         1.13797e+06 |          1 |
| 318657 |         1.13797e+06 |          1 |
| 319060 |         1.13847e+06 |          1 |
| 319061 |         1.13847e+06 |          1 |
| 319065 |         1.13848e+06 |          1 |
| 319066 |         1.13848e+06 |          1 |
| 319363 |         1.13888e+06 |          1 |
| 319364 |         1.13888e+06 |          1 |
| 319463 |         1.13908e+06 |          1 |


Table: Site
|     Id |   CustomerId |   IndustryTypeId | Name     | Site Town   | PostCode   |
|-------:|-------------:|-----------------:|:---------|:------------|:-----------|
| 171601 |       139887 |               68 | Site 51  | Town 51     | CH46 6AD   |
| 171902 |       107262 |               68 | Site 55  | Town 55     | WN8 9DB    |
| 172104 |       140188 |               68 | Site 58  | Town 58     | L35 4LU    |
| 172712 |       140897 |               68 | Site 65  | Town 65     | L4 4EJ     |
| 173317 |       133220 |               68 | Site 74  | Town 74     | L34 5QJ    |
| 174023 |       141501 |               68 | Site 75  | Town 75     | WA1 2NZ    |
| 175339 |       142411 |               68 | Site 101 | Town 101    | CH42 3TF   |
| 175341 |       142413 |               68 | Site 103 | Town 103    | CW7 2GN    |
| 175942 |       142915 |               68 | Site 113 | Town 113    | WA9 4HY    |
| 176953 |       133220 |               68 | Site 118 | Town 118    | L14 7QH    |


Table: User
|   id | name      | surname   |
|-----:|:----------|:----------|
|    1 | Anbast    | Admin     |
|  101 | Michele   | Noronha   |
|  104 | Jenny     | Adair     |
|  108 | Nicci     | Inman     |
|  109 | Liz       | Nicholson |
|  114 | Carolyn   | Delve     |
|  203 | David     | Dawson    |
|  204 | Christine | Lees      |
|  205 | Christine | Carlin    |
|  206 | Charlotte | Duggan    |

 


tables: name: Agreement Dates
data: Agreement Dates.csv
name: Agreement
data: Agreement.csv
name: ContainerGroups
data: ContainerGroups.csv
name: ContainerTypes
data: ContainerTypes.csv
name: ContractPrice
data: ContractPrice.csv
name: ContractPricePeriod
data: ContractPricePeriod.csv
name: Customer
data: Customer.csv
name: CustomerType
data: CustomerType.csv
name: IndustryTypes
data: IndustryTypes.csv
name: Job
data: Job.csv
name: Key Measures
data: Key Measures.csv
name: MaterialTypes
data: MaterialTypes.csv
name: Price
data: Price.csv
name: ServiceSchedule
data: ServiceSchedule.csv
name: ServiceScheduleEntry
data: ServiceScheduleEntry.csv
name: Site
data: Site.csv
name: User
data: User.csv
label: ServiceScheduleEntry
ServiceScheduleId
ServiceSchedule
Id
Site
CustomerId
Customer
Id
Agreement
SiteId
Site
Id
Agreement
ContainerTypeId
ContainerTypes
Id
ContainerTypes
ContainerGroupId
ContainerGroups
id
Customer
CustomerTypeId
CustomerType
Id
Agreement
MaterialTypeId
MaterialTypes
Id
Site
IndustryTypeId
IndustryTypes
Id
ContractPricePeriod
ContractPriceId
ContractPrice
Id
ContractPrice
AgreementId
Agreement
Id
Agreement
SalesRepId
User
id
ServiceSchedule
AgreementId
Agreement
Id
Price
AgreementId
Agreement
Id
Agreement
StartDate
Agreement Dates
Date
Job
AgreementId
Agreement
Id
output: {
    "joins": [
        {
            "from_table": "ServiceScheduleEntry",
            "from_column": "ServiceScheduleId",
            "to_table": "ServiceSchedule",
            "to_column": "Id"
        },
        {
            "from_table": "Site",
            "from_column": "CustomerId",
            "to_table": "Customer",
            "to_column": "Id"
        },
        {
            "from_table": "Agreement",
            "from_column": "SiteId",
            "to_table": "Site",
            "to_column": "Id"
        },
        {
            "from_table": "Agreement",
            "from_column": "ContainerTypeId",
            "to_table": "ContainerTypes",
            "to_column": "Id"
        },
        {
            "from_table": "ContainerTypes",
            "from_column": "ContainerGroupId",
            "to_table": "ContainerGroups",
            "to_column": "id"
        },
        {
            "from_table": "Customer",
            "from_column": "CustomerTypeId",
            "to_table": "CustomerType",
            "to_column": "Id"
        },
        {
            "from_table": "Agreement",
            "from_column": "MaterialTypeId",
            "to_table": "MaterialTypes",
            "to_column": "Id"
        },
        {
            "from_table": "Site",
            "from_column": "IndustryTypeId",
            "to_table": "IndustryTypes",
            "to_column": "Id"
        },
        {
            "from_table": "ContractPricePeriod",
            "from_column": "ContractPriceId",
            "to_table": "ContractPrice",
            "to_column": "Id"
        },
        {
            "from_table": "ContractPrice",
            "from_column": "AgreementId",
            "to_table": "Agreement",
            "to_column": "Id"
        },
        {
            "from_table": "Agreement",
            "from_column": "SalesRepId",
            "to_table": "User",
            "to_column": "id"
        },
        {
            "from_table": "ServiceSchedule",
            "from_column": "AgreementId",
            "to_table": "Agreement",
            "to_column": "Id"
        },
        {
            "from_table": "Price",
            "from_column": "AgreementId",
            "to_table": "Agreement",
            "to_column": "Id"
        },
        {
            "from_table": "Agreement",
            "from_column": "StartDate",
            "to_table": "Agreement Dates",
            "to_column": "Date"
        },
        {
            "from_table": "Job",
            "from_column": "AgreementId",
            "to_table": "Agreement",
            "to_column": "Id"
        }
    ]
}