sql, java. I beg for guidance.

Question

Why I use the enterprise number and user number to query all departments and the number of people under the department, and 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 department table It is one-to-many,
The following is the sql I wrote:

SELECT count(c.id) AS userCount,
b.company_id AS companyId,
b.`name` AS `name`

FROM

company_branch b

LEFT JOIN company_personnel_file c ON c.branch_id = b.id
AND c.user_id =55
AND c. STATUS = 1
WHERE

1 = 1

AND b.company_id =10043
GROUP BY

b.id

LIMIT 0,
10;

Answer 1

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:

1. Your WHERE 1 = 1 is not needed

The
2. 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;