php - MySQL COUNT with GROUP AND JOIN -

i need select information 3 tables 1 sql query. try explain as possible.

i have following 3 tables:

users

id | full name | category_id -------------------------- 1  | john doe  | 3 2  | john doe  | 5 3  | john doe  | 2 4  | john doe  | 3 5  | john doe  | 3 

categories

id | name ---------------------- 2  | category name 1 3  | category name 2 5  | category name 3 

registrations

id | user_id ------------ 1  | 1 2  | 2 3  | 4 

now want outcome of sql query html table looking like:

category | number of registrations
category name 2 | 3
category name 3 | 3
category name 1 | 1

so sum up, have select user_id registrations table, category_id each user users table , find category name categories table.

is doable?

you can try this

select c.name, count(r.user_id)  categories c  inner join users u      on  u.category_id = c.id  inner join registrations r      on r.user_id = u.id  group c.id