Efficient SQL query with subquery and multiple joins

Question

I have the following SQL query that performs a subquery and joins two tables which is then joined with a primary table:

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

The output of the query is:

id | cgso | cgal
----------------
 1 |  2   | 54

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.

Thank You

This should probably be asked on codereview, which is a site for questions about improving, if possible, the already working code. StackOverflow is more about solving problems with the code. — Andriy M
– Andriy M, Commented Sep 10, 2011 at 14:16

Doug Kress · Accepted Answer · 2011-09-10 15:56:17Z

2

You should be able to just simplify the joins...

SELECT a.id, cgso.sf_guard_user_id as cgso, cgal.sf_guard_user_id as cgal
FROM table_a a
INNER JOIN table_c g2 ON g1.group_id = 2
INNER JOIN table_b cgso ON g2.user_id = cgso.sf_guard_user_id  AND cgso.specialty_id = a.specialty_id
INNER JOIN table_c g1 ON g1.user_id = 1
INNER JOIN table_b cgal ON g1.user_id = cgal.sf_guard_user_id AND cgal.specialty_id = a.specialty_id

answered Sep 10, 2011 at 15:56

Doug Kress

3,5471 gold badge15 silver badges19 bronze badges

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

Collectives™ on Stack Overflow

Efficient SQL query with subquery and multiple joins

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