title: Getting all participants with group by and self join
body: <p>I have below table. Here is the whole schema.<a href="https://i.stack.imgur.com/3zHRM.png" rel="nofollow noreferrer">chat_schema</a></p>
  <pre><code>Paricipant
  --------------------------------------
  id | user_id | group_id | is_blocked
  ----------------------------------------
  1 |   20     |   12     | false
  2 |   21     |   12     | false
  3 |   20     |   13     | false
  4 |   22     |   13     | false
  5 |   20     |   14     | false
  6 |   23     |   14     | false
  7 |   24     |   14     | false
  
  User
  --------------------------------------
  id | username 
  ----------------------------------------
  20 |   Mike     
  21 |   Selly     
  22 |   Ruby
  23 |   Catherine
  24 |   Monica
  </code></pre>
  <p>So I need to find all the groups a user belongs, and that group should include other user's who belongs to same group.</p>
  <p>so for user 20, there are groups 12, 13 and 14. I need also the list of users who belongs to the same group.</p>
  <pre><code>where user_id
   group_id (maybe using group_by) 
   list of users with username who belongs to same group except one with user_id
  </code></pre>
  <p>This is what I have tired so far.</p>
  <pre><code>SELECT &quot;ChatParticipant&quot;.&quot;id&quot;, &quot;ChatParticipant&quot;.&quot;group_id&quot;, &quot;Self&quot;.&quot;id&quot; AS &quot;Self.id&quot;, &quot;Self&quot;.&quot;user_id&quot; AS &quot;Self.user_id&quot;, &quot;Self&quot;.&quot;group_id&quot; AS &quot;Self.group_id&quot;, &quot;Self&quot;.&quot;is_blocked&quot; AS &quot;Self.is_blocked&quot;, &quot;Self&quot;.&quot;createdAt&quot; AS &quot;Self.createdAt&quot;, &quot;Self&quot;.&quot;updatedAt&quot; AS &quot;Self.updatedAt&quot;, &quot;Self-&gt;User&quot;.&quot;id&quot; AS &quot;Self.User.id&quot;, &quot;Self-&gt;User&quot;.&quot;username&quot; AS &quot;Self.User.username&quot;
  FROM &quot;ChatParticipants&quot; AS &quot;ChatParticipant&quot;
  LEFT OUTER
  JOIN &quot;ChatParticipants&quot; AS &quot;Self&quot; ON &quot;ChatParticipant&quot;.&quot;group_id&quot; = &quot;Self&quot;.&quot;group_id&quot;
  LEFT OUTER
  JOIN &quot;Users&quot; AS &quot;Self-&gt;User&quot; ON &quot;Self&quot;.&quot;user_id&quot; = &quot;Self-&gt;User&quot;.&quot;id&quot;
  WHERE &quot;ChatParticipant&quot;.&quot;user_id&quot; = 14;
  </code></pre>
  <p>This is what I want. Here <code>user_id</code> will be passed in where clause.</p>
  <pre><code>-----------------------
  user_id | group_id | usernames
  20      | 12       | Selly
  20      | 14       | Catherine, Monica
  20      | 13       | Ruby 
  </code></pre>
  <p>How can I achieve this. Please help If I can do self join and how. Thanks.</p>