如何在匹配过程中避免重复？

Question

How do we match the students with the company by looking at the student's choice of jobscope keeping in mind the no of vacancy available and to avoid duplication?

For example, if the student chose IT as their preferred jobscope and the company's jobscope is IT, how do we come up with the queries in PHP context to display on the website if the company have a vacancy of only 4 students.

Our aim is to assign students to a particular company with the same jobscope on both sides and displaying the name of company the student is assigned to without duplications.

We are not able to post screenshots of the database, so we will list it out.

Table name: student_details
Fields:-
name
jobscope

Table name: job_details
Fields:-
job_title
jobscope
no_of_vacancy

So far, this is the codes that we made from scratch.

<?php
    $con=mysqli_connect("host","user","password","database");
    // Check connection
    if (mysqli_connect_errno())
      {
      echo "Failed to connect to MySQL: " . mysqli_connect_error();
      }
$result13 = mysqli_query($con,"SELECT `admin_no`,`name`,`address`, `jobscope`,`GPA`,`gender` FROM `student_details` WHERE jobscope='IT' ORDER BY GPA DESC;");
echo "<table border='1' >
<tr bgColor=white>
<th>Admission Number</th>
<th>Name</th>
<th>Address</th>
<th>Jobscope</th>
<th>GPA</th>
<th>Gender</th>
</tr>";
while($row13 = mysqli_fetch_array($result13))
  {
echo "<tr>";
echo "<td bgColor=white>" . $row13['admin_no'] . "</td>";
echo "<td bgColor=white>" . $row13['name'] . "</td>";
echo "<td bgColor=white>" . $row13['address'] . "</td>";
echo "<td bgColor=white>" . $row13['jobscope'] . "</td>";
echo "<td bgColor=white>" . $row13['GPA'] . "</td>";
echo "<td bgColor=white>" . $row13['gender'] . "</td>";
echo "</tr>";
  }
echo "</table>";
$result12 = mysqli_query($con,"SELECT `job_id`,`job_title`,`jobscope`,`no_of_vacancy`,`job_address`,`contact_details` FROM`job_details` WHERE jobscope ='IT' ORDER BY `skill_id` ASC;");
echo "<table border='1' >
<tr bgColor=white>
<th>Job ID</th>
<th>Company Name</th>
<th>Jobscope</th>
<th>Number of Vacancy</th>
<th>Address</th>
<th>Contact Details</th>
</tr>";
while($row12 = mysqli_fetch_array($result12))
  {
echo "<tr>";
echo "<td bgColor=white>" . $row12['job_id'] . "</td>";
echo "<td bgColor=white>" . $row12['job_title'] . "</td>";
echo "<td bgColor=white>" . $row12['jobscope'] . "</td>";
echo "<td bgColor=white>" . $row12['no_of_vacancy'] . "</td>";
echo "<td bgColor=white>" . $row12['job_address'] . "</td>";
echo "<td bgColor=white>" . $row12['contact_details'] . "</td>";
echo "</tr>";
  }
echo "</table>";
?>

We did another query to match the students and companies but there were many duplications.

SELECT DISTINCT s.name AS Student, j.job_title AS Company, s.jobscope
FROM student_details AS s,job_details AS j
WHERE s.jobscope = j.jobscope
AND s.status = '1';

Is there any other queries or functions to prevent duplication within the same query itself?

When we run the last query, it will look like this.

Student | Company                      | Jobscope
A2      | Razar (Asia Pacific) Pte Ltd | HR
A6      | Razar (Asia Pacific) Pte Ltd | HR
A10     | Razar (Asia Pacific) Pte Ltd | HR
A14     | Razar (Asia Pacific) Pte Ltd | HR
A18     | Razar (Asia Pacific) Pte Ltd | HR
A22     | Razar (Asia Pacific) Pte Ltd | HR
A2      | OAK 3 Films Pte Ltd          | IT
A7      | OAK 3 Films Pte Ltd          | IT
A11     | OAK 3 Films Pte Ltd          | IT
A15     | OAK 3 Films Pte Ltd          | IT
A19     | OAK 3 Films Pte Ltd          | IT
A2      | CHILDREN'S CANCER FOUNDATION | HR
A6      | CHILDREN'S CANCER FOUNDATION | HR
A10     | CHILDREN'S CANCER FOUNDATION | HR
A14     | CHILDREN'S CANCER FOUNDATION | HR
A18     | CHILDREN'S CANCER FOUNDATION | HR
A22     | CHILDREN'S CANCER FOUNDATION | HR

Why are there duplications for students? Because in the student_details table, each student have chose their own preferred jobscope. But when we run the last query, the jobscope came out differently.

Table: student_details

Admin no | Name | Jobscope
2333456J | A2 | HR 2345677G | A7 | IT

Answer 1

You need to do some aggregation to eliminate what you are calling duplicates.

A query like this will give a list of students for each combination of company and scope.

SELECT j.job_title AS Company, s.jobscope, j.no_of_vacancy
       group_concat(s.name) as students
  FROM student_details AS s
  JOIN job_details AS j ON s.jobscope = j.jobscope
 WHERE s.status = '1'
 GROUP BY j.job_title, s.jobscope, j.no_of_vacancy 
 ORDER BY j.job_title, s.jobscope, j.no_of_vacancy DESC

Note the group_concat and group_by operations.

In the same way, this will show the companies for each student. The companies will be show with the largest number of vacancies first.

SELECT s.name AS student, s.jobscope, 
       group_concat(j.job_title ORDER BY j.no_of_vacancy DESC) AS Companies
 FROM student_details AS s
 JOIN job_details AS j ON s.jobscope = j.jobscope
WHERE s.status = '1'
GROUP BY s.name, s.jobscope
ORDER BY s.name, s.jobscope

This will be kind of messy, because the Companies field might be quite wide. But it will work for you.

Pro tip: join us in the 21st century. Avoid using the comma-list style of JOIN (FROM a,b WHERE a.x=b.x) and use the explicit JOIN (FROM a JOIN b ON a.x=b.x) instead. It's easier to read and works with LEFT JOINs as well.