I have 2 tables. 1 is called coaches, the other coach assignments. coach assignments keeps track of what coach is assigned to a customer. When new customers come in I want the coaches with the fewest assignment at the top of a list.
coaches +user_id +bio +title coach_assignments +id +client_id +coach_id +deleted_at
I wrote this code to get the count from the coach_assignments table
select count(coach_id), coach_id from coach_assignments where deleted_at is not null group by coach_id order by count(coach_id) ASC
and it worked, however the glaring hole is that a new coach doesn't have any entries in coach_assignments so they aren't counted. I don't want to add a client_count column to coaches as I don't feel coaches should have that info.
select user_id, count(coach_id) from coaches left join ( SELECT count(coach_id), coach_id from coach_assignments where deleted_at is not null group by coach_id order by count(coach_id) ASC ) as counted ON coaches.user_id = counted.coach_id;
but this only returned 1 row and there should be 4 for my test data. How do I do something like this?