the length is:

mysql> select count(*) from projects join(select project_skills.skill_id from project_skills where project_skills.skill_id = 7) as s on projects.id = s.skill_id;
 ---------- 
| count(*) |
 ---------- 
|      869 |
 ---------- 

another length is:

mysql> select count(*) from projects join(select project_frameworks.framework_id from project_frameworks where project_frameworks.framework_id = 3) as f on projects.id = f.framework_id;     

 ---------- 
| count(*) |
 ---------- 
|      264 |
 ---------- 

I need to combine these length(result).
So I came up:

select count(*) from projects join(select project_skills.skill_id from project_skills where project_skills.skill_id = 7) as s on projects.id = s.skill_id join(select project_frameworks.framework_id from project_frameworks where project_frameworks.framework_id = 3) as f on projects.id = f.framework_id;

But the length is 0....
How can I reach my ideal?

■ Table Construction

projects vs project_skills(one to many)
skills vs project_skills(one to many)
projects vs project_frameworks(one to many)
frameworks vs project_frameworks(one to many)

project_skill（column：id、project_id、skill_id）
project_frameworks（column：id、project_id、framework_id）

CodePudding user response：

You can sum it up using union all to get the total count.

SELECT sum(q.c) FROM (
select 
  count(*) as c
from 
  projects 
     join( select  project_skills.skill_id from project_skills where project_skills.skill_id = 7 ) as s 
     on projects.id = s.skill_id 
union all 
select 
  count(*) as c
from 
  projects 
  join( select project_frameworks.framework_id  from project_frameworks where project_frameworks.framework_id = 3 ) as f 
  on projects.id = f.framework_id
  ) q;