CODEWITHSUNDEEP
sqlsqlite
Matheus Hatje
Matheus Hatje

Reputation: 987

SQLite return multiple rows in a single one

Im pretty new to SQL, and basically I have two tables messages and messagesData:

messages:

_id    message      sender    conversationId

 1     "text"       'user1'     111111
 2     "otherText"  'user2'     111111
 3     "bla"        'user3'     111111

messagesData

messageId   user     read           received

1           'user1'  1574939944809  1574939944809
1           'user2'  1574939944809  1574939944809
1           'user3'  1574940002929  1574940002929
2           'user1'  1574940002929  1574940002929
2           'user2'  false          1574940002929
2           'user3'  false          1574940002929
3           'user1'  1574940097707  1574940097707
3           'user2'  false          1574940097707
3           'user3'  false          1574940097707

And i need to make a query that returns in a single row all the data from the message, and "aggregate" the data from messagesData with the same messageId, something like this:

_id    message   sender   conversationId    user                     read                                         received
1      "text"    'user1'   111111            'user1','user2','user3'  1574939944809,1574939944809,1574940002929     1574939944809,1574939944809,1574940002929

I tried using the solution from this question, but as you can see in my case, there is more than 2 data that I want to aggregate ( the amount of messagesData is variable per message)

Upvotes: 0

Views: 902

Answers (1)

forpas
forpas

Reputation: 164099

You can aggregate and use group_concat() to return the values from messagesdata as a comma separated list:

select m._id, m.message, m.sender, m.conversationId,
  group_concat(d.user) user,
  group_concat(d.read) read,
  group_concat(d.received) received
from messages m left join messagesdata d
on d.messageid = m._id
where m._id = 1
group by m._id, m.message, m.sender, m.conversationId

See the demo.
Results:

| _id | message   | sender | conversationId | user              | read                                      | received                                  |
| --- | --------- | ------ | -------------- | ----------------- | ----------------------------------------- | ----------------------------------------- |
| 1   | text      | user1  | 111111         | user1,user2,user3 | 1574939944809,1574939944809,1574940002929 | 1574939944809,1574939944809,1574940002929 |

Upvotes: 1

Related Questions