PostgreSQL select query with subquery

Question

First of all I must apologize for bad topic. I didn't have any better idea.

For my internal messaging on my web page I have the following tables:

messages (id, subject, owner_id) messages_recipients (id, recipient_id, message_id)

I need to get all messages for specific user with one additional field, that contains all recipients ids for specific message.

For example:

messages: id | subject | owner_id 1 | test 1 | 1 2 | test 2 | 2 messages_recipients: id | recipient_id | message_id 1 | 10 | 1 2 | 11 | 1 3 | 10 | 2

Expected result is (messages for user 10):

message_id, subject, all users that receive message with this id 1, test 1, 10;11; 2, test 2, 10;

I want to list all messages for specific user. For implementation reply all I also need information which users receive specific message.

mlinth · Accepted Answer · 2015-01-22 18:12:49Z

Think this will do it:

SELECT r.message_id,subject,string_agg(r.recipient_id::text,';') FROM messages m INNER JOIN messages_recipients r ON r.message_id = m.id INNER JOIN (SELECT distinct message_id from messages_recipients WHERE recipient_id = 10) u ON u.message_id = r.message_id GROUP BY r.message_id,subject

Collectives™ on Stack Overflow

PostgreSQL select query with subquery

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related