Show, Join, Count, Grouping 6 Tables on PHP MySQL

234

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:

+

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

982

Answer

+
148

Answer

-------+
303

Answer

-------+
923

Answer

-----+
699

Answer

--+
201

Answer

-------+ | slug_job | name_level | name_department | name_location | name_position | total applicant | +
614

Answer

+
540

Answer

-------+
80

Answer

-------+
592

Answer

-----+
210

Answer

--+
899

Answer

-------+ | 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 | +
110

Answer

+
54

Answer

-------+
105

Answer

-------+
702

Answer

-----+
262

Answer

--+
242

Answer

-------+
635

Answer

Solution:

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

People are also looking for solutions to the problem: php - Codeigniter - issue in setting flashdata

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.