Select count from sub table
count, join, mysql, performance, select
Solution
Try using a `GROUP BY`, like this:
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
Problem
I need to get the count from one of the sub tables / joined 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 need to select from user table various fields like `id`, `name`, `etc` along with the count of various user id in `exercise` table. For example, in the above case I need the output: ``` id name count ------------------------------------------- 1 u1 2 --since two u1's are present in exercise 2 u2 0 --since no u2's are present in exercise ``` I tried this: but I get 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 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 a sub query, but is painfully slow (5 to 13 seconds for about 10000 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 ``` Is there a way I can get the `count` info in one single query, with the help of `join` or so? Sub query is very slow for my needs.