1

I have two tables, ChatRoom and ChatRoomMap, I want to get a list of chatrooms a user belongs to, along with all the other users in each chatroom.

// this contains a map of user to chatroom, listing which user is in what room
CREATE TABLE ChatRoomMap
(
  user_id bigint NOT NULL,
  chatroom_id text NOT NULL,
  CONSTRAINT uniq UNIQUE (userid, roomid)
)
// sample values
==========================
| user_id  | chatroom_id | 
|    1     |      7      |
|    1     |     blue    |
|    7     |     red     |
==========================

And

CREATE TABLE ChatRoom 
(
  id text NOT NULL,
  admin bigint,
  name text,
  created timestamp without time zone NOT NULL DEFAULT now(),
  CONSTRAINT uniqid UNIQUE (id)
)
// sample values
======================================================
|    id    |    admin    |    name      |  timestamp |
|   blue   |      7      |   blue room  |    now()   |
|   red    |      2      |    red       |    now()   |
|    7     |     11      |   mine       |    now()   |
======================================================

To get a list of rooms a user is in, I can do:

SELECT DISTINCT ON (id) id, userid, name, admin 
FROM ChatRoomMap, ChatRoom WHERE ChatRoomMap.user_id = $1 AND ChatRoomMap.chatroom_id = ChatRoom.id

This will get me a distinct list of chat rooms a user is in.

I would like to get the distinct list of rooms along with all the users in each room (concatenation of all as a separate column), how can this be done?

Example result:

=======================================================
| user_id  | chatroom_id | name | admin | other_users |
|    10    |     7       | One  |   1   | 1, 2, 3, 8  |
|    10    |     4       | AAA  |   10  | 7, 11, 15   |
=======================================================
SoluableNonagon
  • 11,541
  • 11
  • 53
  • 98
  • Do you want a list of all unique rooms with a count of users in them or a list of users, or a user in each column? – blairmeister Nov 27 '15 at 22:25
  • Do you want string concatenation of all other users in that separate column or each user in each column? How many users are we talking about? – Parfait Nov 27 '15 at 22:25
  • @blairmeister I'm looking to get a list of all the rooms a user is in, plus an additional column which lists all the other users in a room. – SoluableNonagon Nov 30 '15 at 14:41
  • @Parfait, I was looking for a concatenation of users in a separate column. This could be any number of users really. But realistically speaking it would be under 10. – SoluableNonagon Nov 30 '15 at 14:43

1 Answers1

3

First up, use proper joins - the explicit join syntax was introduced to the SQL92 standard and the major vendors implemented it in the early 2000's (and it's the only way to achieve an outer join).

Try this:

SELECT DISTINCT id, crm2.user_id, name, admin, 
FROM ChatRoomMap crm1
JOIN ChatRoom ON crm1.chatroom_id = ChatRoom.id
LEFT JOIN ChatRoomMap crm2 ON crm2.chatroom_id = crm1.chatroom_id
    AND crm2.user_id != crm1.user_id -- only other users
WHERE crm1.user_id = $1

The LEFT JOIN is needed in case there are no other users in the room it will still list the room (with a null for other user id).

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • Thank you for your answer, can you elaborate on what you mean by syntax being superseded? I didn't know implicit joins were bad syntax. Is it just for the purpose of readability? – SoluableNonagon Nov 30 '15 at 14:27
  • I would also like to know how much of an exaggeration 20 years is, I've learned to do implicit join in my CS class like 10 years ago. I'm pretty sure it was already considered bad practice at that point, but I'm wondering just how far behind our teacher was. – Julien Blanchard Nov 30 '15 at 14:48
  • Well, I read that things like SQL Server stopped supporting it around 2008, but I don't know if that is actually correct. – SoluableNonagon Nov 30 '15 at 15:00
  • The ANSI-syntax standard of 1992 introduced explicit joins as the mainstay in joining related tables. Most query optimizers do not differ in performance of either but readability and maintainability is a deciding factor. There are many hearty SO posts on [inner join vs where](http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause). – Parfait Nov 30 '15 at 15:00
  • @JulienBlanchard If you were using Oracle, `WHERE` clause joins were the norm. I don't think Oracle even introduced support for the `LEFT` and `RIGHT` syntax until 9i. – Glenn Nov 30 '15 at 15:14
  • @Glenn That was exactly the case. Can't recall the exact version we were using, but it was definitely Oracle. – Julien Blanchard Nov 30 '15 at 15:22
  • @Glenn: you are right, support for the explicit `JOIN` operator was added to Oracle in 9i - which was released **14 years** ago. Enough time to get used to the more robust and modern `JOIN` syntax I'd say ;) –  Nov 30 '15 at 15:26
  • @a_horse_with_no_name interesting. I just trawled posgres release history and found that for postgres `JOIN` was introduced **15 years** ago in [release 7.0](http://www.postgresql.org/docs/current/static/release-7-0.html) - a similar time frame to Oracle. "20 years" is overstating it (not by *that* much though) - answer updated. – Bohemian Nov 30 '15 at 21:39
  • @JulienBlanchard the "*more than 20 years*" refers to the introduction of joins in the SQL standard (1992), not in a specific DBMS product. –  Nov 30 '15 at 22:09