title: Efficient SQL query with subquery and multiple joins
body: <p>I have the following SQL query that performs a subquery and joins two tables which is then joined with a primary table:</p>
  
  <pre><code>SELECT a.id, cgso.sf_guard_user_id as cgso, cgal.sf_guard_user_id as cgal
  FROM table_a a
  JOIN (  SELECT cgso.sf_guard_user_id, cgso.speciality_id
          FROM table_c g
          JOIN  table_b as  cgso
              ON g.user_id = cgso.sf_guard_user_id and g.group_id = 2) as cgso
      ON a.speciality_id = cgso.speciality_id
  JOIN (  SELECT cgal.sf_guard_user_id, cgal.speciality_id
          FROM table_c g
          JOIN  table_b as  cgal
              ON g.user_id = cgal.sf_guard_user_id and g.group_id = 1) as cgal
      ON a.speciality_id = cgal.speciality_id
  </code></pre>
  
  <p>The output of the query is:</p>
  
  <pre><code>id | cgso | cgal
  ----------------
   1 |  2   | 54
  </code></pre>
  
  <p>The output is fine and as intended, however is there a more efficient way to get the same output? Any tips or advice would be much appreciated.</p>
  
  <p>Thank You</p>