CODEWITHSUNDEEP
phpmysqlselect
MrWhddite333
MrWhddite333

Reputation: 1851

Count users from table 2 in request to table 1

I have table with positions 

tbl_positions
   id position
    1 Driver
    2 Lobby
    3 Support
    4 Constructor

and in other table i have users

tbl_workers
  id   name   position
   1   John   2
   2   Mike   3
   3   Kate   2
   4   Andy   1

i do request of positions 

SELECT position FROM tbl_positions

but i also need to show how many workers are assigned to each position i tried to do separate request

SELECT id FROM tbl_workers WHERE position = 2

but cannot display all together in table cannot bind number of users to position.

How can i make join this queries into one, so it also show positions without workers assigned?

Upvotes: 0

Views: 47

Answers (1)

Mike Haboustak
Mike Haboustak

Reputation: 2296

join and group by

SELECT p.id, p.position, count(*) FROM  tbl_positions as p
inner join tbl_workers as w on w.position=p.id
group by p.id, p.position

Upvotes: 1

Related Questions