MYSQL fetch from same table?

Question

MySQL

Hello.
It was necessary to select the number of objects for each user, such a request could be formulated:

$statement = $connection->prepare("SELECT DISTINCT(usr.name), usr.id, c.cnt, d.excnt FROM users usr 
LEFT JOIN (SELECT COUNT(*) as cnt, obj.realtor_id FROM objects obj GROUP BY realtor_id) as c ON usr.id=c.realtor_id
LEFT JOIN (SELECT COUNT(*) as excnt, obj.realtor_id FROM objects obj WHERE obj.exclusive=1 GROUP BY realtor_id) as d ON usr.id=d.realtor_id");

Now the task is complicated by the fact that everyone can have a manager who is in the same table (users). To do this, we created the managerId variable.
Now you need to display the same number of objects, but for each manager, and not for the user, I try this:

$statement = $connection->prepare("SELECT DISTINCT(usr1.name), usr1.id, c.cnt, d.excnt FROM users usr1 
LEFT JOIN (SELECT usr.id, usr.manager_id FROM users usr) as b ON usr1.id=b.manager_id
INNER JOIN (SELECT COUNT(*) as cnt, obj.realtor_id FROM objects obj GROUP BY realtor_id) as c ON b.id=c.realtor_id
INNER JOIN (SELECT COUNT(*) as excnt, obj.realtor_id FROM objects obj WHERE obj.exclusive=1 GROUP BY realtor_id) as d ON b.id=d.realtor_id");

but for each manager there are several records (i.e. duplicates), although DISTINCT is worth it.
I think that the problem is easily fixable, but I can not figure out how

Answer 1

It is difficult to understand the structure and logic of the work, but I will try to assume that the managerId field in the users table will be null for managers.

SELECT USR1.NAME, 
       USR1.ID, 
       C.CNT, 
       D.EXCNT 
  FROM USERS USR1
  LEFT JOIN (SELECT COUNT (*) AS CNT, OBJ.REALTOR_ID 
  	           FROM OBJECTS OBJ 
  	           GROUP BY REALTOR_ID) AS C ON USR.ID = C.REALTOR_ID
  LEFT JOIN (SELECT COUNT (*) AS EXCNT, OBJ.REALTOR_ID 
  	           FROM OBJECTS OBJ WHERE OBJ.EXCLUSIVE = 1 
  	           GROUP BY REALTOR_ID) AS D ON USR.ID = D.REALTOR_ID;
 WHERE MANAGER_ID IS NULL