I have three tables users, skills and user_skills

users table with columns:

id,name,sex

skills table with columns:

id,name,view_count

and user_skills table with columns:

id,user_id,skill_id

I want to output total count of skills based on user selection which are store on user_skill expected output table is:

I have tried this SQL query

select `skills`.*, (select count(*) from `user_skills`
                    where `skills`.`id` = `user_skills`.`skill_id`) as `user_skill_count`
from `skills`

but it returns other skills which user has not selected and output count 0 as table below

I want the results to be only the skill users has selected.

CodePudding user response：

Just use a simple join in your query:

SELECT `skills`.`name`, count(*) as `user_skill_count`
FROM `skills`
INNER JOIN `user_skills` ON `skills`.`id` = `user_skills`.`skill_id`
GROUP BY `skills`.`id`, `skills`.`name`

CodePudding user response：

You want to select records in one table based on the conditions in another one. Maybe this thread will help you?

Select records in on table based on conditions from another table?