Select a counter from a subcategory

Question

I need to get a counter from one of the subcategories / federated tables involved in the query. I will demonstrate with a simple example:

Table: user

    id             name            etc
-------------------------------------------
    1               u1
    2               u2

Table: Exercise

    id             userId            etc
-------------------------------------------
    1               1
    2               1

Now I have to choose from a variety of user fields of the table, such as id, name, etc, and also the number of different user IDs in the tableexercise .

For example, in the above case, I need the output :

    id             name            count
-------------------------------------------
    1               u1               2 --since two u1 are present in exercise
    2               u2               0 --since no u2 are present in exercise

I tried this: but I get a syntax error:

--actual query is very complex
SELECT u.id, u.name, COUNT(e.*)
FROM   user AS u
JOIN   exercise AS e ON u.id =  e.userId 

I tried this: but I get the syntax error again:

--actual query is very complex
SELECT u.id, u.name, (SELECT COUNT(*) FROM e)
FROM   user AS u
JOIN   exercise AS e ON u.id =  e.userId 

This works as an auxiliary query, but is very slow (from 5 to 13 seconds for about 10,000 rows in each table):

--actual query is very complex
SELECT u.id, u.name, (SELECT COUNT(*) FROM exercise WHERE e.userId = u.id)
FROM   user AS u

count join ? - .

Answer 1

GROUP BY, :

SELECT u.id, u.name, COUNT(e.userId)
FROM   user AS u
LEFT JOIN exercise AS e 
ON u.id =  e.userId
GROUP BY u.id 

Answer 2

:

SELECT u.id, u.name, COUNT(e.userId)
FROM  user AS u
LEFT JOIN exercise AS e 
ON u.id =  e.userId
GROUP BY u.id,u.name

user, exercise .