Marc Zaharescu Marc Zaharescu - 1 year ago 75
SQL Question

Combine two complex SQL statements into one

I have the following 2 SQL statements:

Select count( as '# Projects', as ' Country name' from project, country c2, project_country where project_country.project_id = and project_country.country_id= group by;

select count( as '# Consultants', as ' Country name' from user,consultant_profile, country c1 where user.country_id and user.is_active=1 and user.type=0 and = consultant_profile.user_id group by ;

Is it possible to combine the two queries based on the
field so I have only one result with #Projects, #Consultants and Country Name?

Answer Source

You could join the country table on two aggregate queries on the other tables:

SELECT AS country_name, num_projects, num_consultants
FROM   country c
JOIN   (SELECT   pc.country_id, COUNT( AS num_projects
        FROM     project p
        JOIN     project_country pc ON = pc.projcet_id
        GROUP BY pc.country_id) x ON x.country_id =
JOIN   (SELECT   u.country_id, COUNT( AS num_consultants
        FROM     user u
        WHERE    u.is_active = 1 AND u.type = 0
        GROUP BY u.country_id) y ON y.country_id =
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download