0
votes

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 email
360 110 test2 email
360 5 test2 phone

Contacts

id results_id name email
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?