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;
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 neededstatement 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 theWHERE
clauseThe modified SQL is as follows: