MySQL inner join a count from another table

Question

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.

OlivierH · Accepted Answer · 2014-07-03 15:40:22Z

6

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

edited Jul 3, 2014 at 15:40

answered Jul 3, 2014 at 15:35

OlivierH

3,8801 gold badge22 silver badges33 bronze badges

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

4 Comments

ttzn Over a year ago

Upvoted, but I'd make it COUNT(DISTINCT p.id) just in case :)

OlivierH Over a year ago

I personnally prefer COUNT(1), but you are right that's clearer for beginners. I edit my answer, thanks.

ngplayground Over a year ago

@OlivierH what does the 1 represent inside the brackets?

OlivierH Over a year ago

If you make, for example, SELECT 1 FROM people, it will return you a '1' character for each line found. Here, use COUNT(1) or COUNT(p.id) does the same thing : count the number of lines found. Use 1 is a common pratice, slightly more performant.

Collectives™ on Stack Overflow

MySQL inner join a count from another table

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related