SQL SELECT and COUNT of second table
count, group-by, left-join, mysql, sql
Solution
If `COUNT` is all you want, you can do it with a subquery rather than a join, like this:
SELECT u.*
, (SELECT COUNT(*) FROM notes WHERE notes.user_id=u.id) as amountnotes
FROM users u
ORDER BY amountnotes DESC
If you do it the `JOIN` way, your `GROUP BY` should list all columns of the `user` (although MySql is one of the RDBMS systems that does not require this, porting such SQL to databases of other vendors becomes problematic).
Problem
Having 2 tables: ``` users ========== id name notes ========= id user_id note ``` Users can have multiple notes. I would like to do a SQL query that gets all users with all their fields but also the amount of notes. Further I would like the result to be ordered by the amount of notes (desc) I wrote this, but I want to know if it's is the correct/fastest way to solve this: ``` SELECT u.*, count(n.id) as amountnotes FROM users AS u LEFT JOIN notes AS n ON u.id=n.user_id GROUP BY u.id ORDER BY amountnotes DESC ``` `EXPLAIN` gives me 2 results. "Using temporary; Using filesort" on users and "Using where; Using join buffer (Block Nested Loop)" on notes Any help appreciated, thanks. If suggesting a different approach please explain why so I can understand.