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

1

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

edited Jan 22, 2015 at 18:12

answered Jan 22, 2015 at 14:41

mlinth

3,1586 gold badges35 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL select query with subquery

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related