sql, java. I beg for guidance.

Question

Why do I use the enterprise number and user number to query all departments and the number of people under the department? Only one piece of data is returned. The database table design is: the user table and the enterprise table are one-to-one, and the enterprise table and the department table are one-to-many. The following is the sql I wrote: {code...} FROM {code...} LEFT JOIN c...

为情所困 · Answer

Since you did not explain the table names of the enterprise table and department table, and there is only one table related to the enterprise or department in the SQL, I can only guess that it is the department table.
Based on this assumption, let’s talk about your SQL. Your SQL has the following problems:

Your WHERE 1 = 1 is not needed
ON
statement in LEFT JOIN is used to limit the data of the table in the left join, not the result data. To limit the result data, you must use the WHERE clause

The modified SQL is as follows:

SELECT
    COUNT(c.id) userCount,
    b.company_id companyId,
    b.name name
FROM
    company_branch b
LEFT JOIN
    company_personnel_file c ON c.branch_id = b.id
WHERE
    c.user_id = 55 AND
    c.STATUS = 1 AND
    b.company_id = 10043
GROUP BY
    b.id
LIMIT 0, 10;