MySQL inner join a count from another table

mysql, php

Solution

You don't have to make the count in the subquery : you can directly select count and then group by. Moreover, you don't have to manually precise the `people` `job_id` : a JOIN is made directly between two tables fields (minimum).

SELECT j.id, j.job_name, count(p.id) as nb_people
  FROM job j
       INNER JOIN people p ON p.job_id = j.id
 WHERE j.id = '1'
GROUP BY j.id, j.job_name
ORDER BY id ASC

Have a look at MySQL aggregate functions documentation : http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html

And maybe another look on JOIN documentation : http://dev.mysql.com/doc/refman/5.7/en/join.html

Problem

I am trying to do a `SELECT` query in MySQL which will also do a `count` in another table and `join` the answer into my initial `table` Table `people` id | name | hair_color | job_id Table `job` id | job_name ``` SELECT * FROM job j INNER JOIN (SELECT COUNT(job_id) AS totals FROM people p WHERE p.job_id='1') ON j.count = totals WHERE id = '1' ORDER BY id ASC ``` So I am trying to get the above query to Select from my `job` table by the `id` and also do a `count` in the `people` table and add the column `count` to my `job` result.

Original source