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
我希望第三个查询在同一个表中显示来自前两个查询的信息,分别列为:行业名称、#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
1 个解决方案
#1
2
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
#1
2
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