MySQL Subqueries

Question

I am facing an interesting problem.

Please see the picture of the two table I have.
userskill table

Jobskill Table

What I want is I want to return the skill names of the userid 17 and the number of jobs in that skills. So the result should be something like this

Skill                                NumOfJobs
Advertising and Promotions Manger        2
Advertising Sales Agent                  8

Is that possible with one query or I should run two query? Thank you in advance..

Sirko · Accepted Answer · 2017-06-03 16:06:58Z

5

SELECT `skill`, COUNT(*) AS `NumOfJobs`
FROM `jobSkill`
INNER JOIN `userSkill` USING ( `skill` )
WHERE `userSkill`.`user`=17
GROUP BY `skill`

Basically this restricts your userSkill table to those rows, with user equal to 17.

Afterwards, we take all those skills and join to jobSkill to get all jobs with the respective skill.

Finally using GROUP BY and COUNT() we tell the database to count the entries per skill.

answered Jun 3, 2017 at 16:06

Sirko

74.3k19 gold badges157 silver badges194 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

That is an awesome answer brother. It works. By the way, just to know is there any other ways to return? Thank you a lot.

"other ways to return" - what do you mean by that?