在PHP MySQL上显示，加入，计数，分组6个表

Question

i have a problem when showing, join, and grouping 6 tables with php and mysql.

My Table

• loker_job: id_job, slug_job, position, department, level, location

• loker_department: id_department, name_department

• loker_level: id_level, name_level

• loker_location: id_location, name_location

• loker_applicant: id_job, file

• loker_user: id_user, name

My Query

SELECT 
loker_job.slug_job,
loker_level.name_level,
loker_location.name_location,
loker_department.name_department,
loker_user.name,
loker_applicant.file,
count(*) as total_applicant
FROM
loker_job
LEFT JOIN loker_department ON loker_department.id_department =    loker_job.department
LEFT JOIN loker_level ON loker_level.id_level = loker_job.`level`
LEFT JOIN loker_location ON loker_location.id_location = loker_job.location
LEFT JOIN loker_user ON loker_user.id_user = loker_job.publisher
LEFT JOIN loker_applicant ON loker_applicant.id_job = loker_job.slug_job
GROUP by loker_job.slug_job

I want the result like this:

+----------+-----------------+-----------------+---------------+----------------------+-----------------+
| slug_job | name_level      | name_department | name_location | name_position        | total applicant |
+----------+-----------------+-----------------+---------------+----------------------+-----------------+
| 1111     | General Manager | Marketing       | America       | Vice General Manager | 3               |
| 2222     | E-officer       | Ecommerce       | Japan         | Officer              | 1               |
| 3333     | General Manager | Sales           | Rusia         | Vice General Manager | 0               |
| 4444     | General Manager | Marketing       | America       | Vice General Manager | 3               |
+----------+-----------------+-----------------+---------------+----------------------+-----------------+

Result

but, the result i get are:

• There are one or more row always result 1 total applicant although from db show 0 or nothing
• the total row not correct, it always -1 from total data

any help will be so appreciated, thank you

Answer 1

First grou by "loker_user" table and left outer join with relational table. And as per below described:

SELECT loker_job.slug_job, loker_level.name_level, loker_location.name_location,
loker_department.name_department, A.name, loker_applicant.file, IFNULL(A.total_applicant,0) as total_applicant
FROM loker_job
LEFT OUTER JOIN loker_department ON loker_department.id_department =   loker_job.department
LEFT OUTER JOIN loker_level ON loker_level.id_level = loker_job.`level`
LEFT OUTER JOIN loker_location ON loker_location.id_location = loker_job.location
LEFT OUTER JOIN loker_applicant ON loker_applicant.id_job = loker_job.slug_job
LEFT OUTER JOIN (
select id_user,name, count(*) as total_applicant from loker_user group by id_user, name
) as A ON A.id_user = loker_job.publisher