CODEWITHSUNDEEP
mysql
TylerB
TylerB

Reputation: 15

MySQL join count of related table

I would like to count the number of members in a project and join that to the results of querying for all projects.

I've tried messing around with sub queries and joins but haven't managed to achieve what I'm looking for yet.

Here's my tables:

project

project_id | name | description | start_date | end_date

project_members

user_id | project_id

For example, project 1 has 2 members and project 2 has 5 members.

Desired output of the query:

project_id | name | description | start_date | end_date | number_of_members
---------------------------------------------------------------------------
1          | xxx  | xxxxxxxxxxx | xxxxxxxxxx | xxxxxxxx | 2
2          | xxx  | xxxxxxxxxxx | xxxxxxxxxx | xxxxxxxx | 5

Any help is greatly appreciated, Thanks!

Upvotes: 1

Views: 33

Answers (1)

kmoser
kmoser

Reputation: 9273

Select the columns you want from project. The COUNT(project_members.user_id ) will give you the number of users in each project if you do the LEFT JOIN as shown.

SELECT
  project_id, name,
  description,
  start_date,
  end_date,
  COUNT( project_members.user_id ) AS number_of_members
FROM project
LEFT JOIN project_members ON
  ( project_members.project_id = project.project_id )
GROUP BY
  project.project_id

Upvotes: 1

Related Questions