CODEWITHSUNDEEP
mysqlsql
Marc Zaharescu
Marc Zaharescu

Reputation: 639

SQL: Combine two queries into a single one

I have the following two queries: 

    Select count(project.id) as num_project , 
           industry.name as industry_name 
    from   project, project_industry, industry 
    where  industry.id= project_industry.industry_id and 
           project.id = project_industry.project_id  
    group by industry.id;

Select count(user.id) as num_consultants , 
       industry.name as industry_name 
from   consultant_profile, industry_experience,user, industry 
where  industry_experience.consultant_profile_id = consultant_profile.id
       and industry_experience.industry_id= industry.id 
       and user.type=0 
       and user.is_active=1 
       and consultant_profile.user_id=user.id 
group by industry_id;

And I was trying to combine them together into a single one as following:

SELECT i.name AS industry_name, num_projects, num_consultants
FROM   industry i
JOIN   (SELECT   pc.industry_id, COUNT(p.id) AS num_projects
        FROM     project p
        JOIN     project_industry pc ON p.id = pc.project_id
        GROUP BY pc.industry_id) x ON x.industry_id = i.id
JOIN   (SELECT   y.industry_id, COUNT(u.id) AS num_consultants
        FROM     user u, consultant_profile cp
        JOIN     project_experience pe on pe.consultant_profile_id = cp.id
        WHERE    u.is_active = 1 AND u.type = 0
        GROUP BY y.industry_id) z ON z.industry_id = i.id;

But it doesn't seem to work. Could anybody point out what I am doing wrong? Any help is much appreciated.

EDIT: To make it clear the first query displays the number of project related to each industry. The second query displays the number of consultants related to each industry.

I want the third query to display the information from the first two queries in the same table on 3 separate columns: Industry Name, #Projects, #Consultants

The table structures is as following, where -> describes the primary and foreign keys of the tables:

Project -> project.id
Project_industry -> project_industry.id, project_id, industry_id
Industry -> industry.id

user -> user.id
Consultant_profile -> consultant_profile.id, user_id
Industry_Experience -> industry_experience.id, consultant_profile_id, industry_id
Industry -> industry.id

Upvotes: 3

Views: 3966

Answers (1)

Shirish Bari
Shirish Bari

Reputation: 2722

You were almost there.As per my understanding below query should work for you .

QUERY

SELECT 
    T1.industry_name ,
    num_project ,
    num_consultants  
FROM 
    ( 
        SELECT 
            COUNT(project.id) AS num_project, 
            industry.name AS industry_name,
            industry.id AS id  
        FROM 
            project, 
            project_industry, 
            industry 
        WHERE 
            industry.id = project_industry.industry_id AND 
            project.id = project_industry.project_id  
        GROUP BY industry.id 
    ) T1 
    JOIN 
        ( 
            SELECT 
                COUNT(user.id) AS num_consultants, 
                industry.name AS industry_name,
                industry.id AS id 
            FROM 
                consultant_profile, 
                industry_experience,
                user, 
                industry 
            WHERE 
                industry_experience.consultant_profile_id = consultant_profile.id AND 
                industry_experience.industry_id = industry.id AND 
                user.type = 0 AND 
                user.is_active = 1 AND 
                consultant_profile.user_id = user.id 
            GROUP BY 
                industry_id 
        ) T2 ON T1.id = T2.id

Upvotes: 2

Related Questions