I am trying to build an sqlite query against a database which I have no control over, i.e. I can't change the odd way data is currently stored.
My data is spread across several tables, and one in particular is causing me issues; contacts.
The tables I am interested in are structured as follows (examples just include what I care about);
Results
| id | state | account |
|---|---|---|
| 5 | 0 | 102 |
| 11 | 2 | 62 |
Data
| id | results_id | type | date | contact_id |
|---|---|---|---|---|
| 1 | 5 | 1033 | 1596664666 | 360 |
| 2 | 11 | 1034 | 1596452446 | 32 |
Contacts_list
| id | contact_id | key | type |
|---|---|---|---|
| 32 | 12 | test | |
| 360 | 110 | test2 | |
| 360 | 5 | test2 | phone |
Contacts
| id | results_id | name | |
|---|---|---|---|
| 12 | 231 | Test Account | [email protected] |
| 110 | 726 | Test Account 2 | [email protected] |
| 5 | 6 | Test Account no | 01234567890 |
So what I want to do is:
- Query the data table to grab all the values in that table, lookup type from the relevant table.
- join the results table (Data.results_id = Results.id) and lookup the relevant account and state details
- Get the contacts name and email from the contacts table.
The last bit is what is throwing me. Data.contact_id = Contacts_list.id and then Contacts_list.contact_id = Contacts.id
If I do a left join on Contacts_list I get additional rows because Contacts_list can have multiple rows for the same ID and I just want as many rows as the Data table has. What I want is to get concat multiple contacts into a single cell, eg;
| account | state | type | date | contact |
|---|---|---|---|---|
| 102 | 0 | 1033 | 1596664666 | Test Account ([email protected]) |
| 62 | 2 | 1034 | 1596452446 | Test Account 2 ([email protected]), Test Account no (01234567890) |
I feel like there will be an easy solution, but I am scratching my head at the minute... Any ideas?