Join tables horizontally and dynamically

Question

I need some help from joining tables horizontally my tables are

+---------+---------+
| Candidates Table  |
+---------+---------+
| can_id   | Name   | 
+---------+---------+
| 1       | Liza    |
| 2       | Sarah   |
| 3       | Jane    |
|         |         |
+---------+---------+

+---------+---------+
| Judges Table      |
+---------+---------+
| id      | Name    | 
+---------+---------+
| 1       | judge1  |
| 2       | judge2  |
| 3       | judge3  |
+-------------------+

+---------+---------------+--------+-------+
|                Score Table               |
+---------+-------+------------------------|
| sco_id  |  can_id| jud_id |crit_id |score|
+---------+--------+-----------------------+
| 1       |   1    |   2    |    1   |  87 |
| 2       |   1    |   3    |    1   |  89 |
| 3       |   1    |   1    |    1   |  80 |
+------------------------------------------+

I need an output of something like this one..

+---------+---------------+-------------+
|                Score board            |
+---------+---------+-------------------|
| Name    |  judge1 | judge2  | judge3  |
+---------+---------+-------------------|
| Liza    |   80    |    87   |    89   |  
|some data|some data|some data|some data|  
|some data|some data|some data|some data|  
+---------------------------------------+

notes: crit_id is criteria id from criteria table.

Normally I would use some joins and subqueries but my problems is I need the output dynamically where in if I add a new judges it will automatically generate a new column. I need at least 1 candidate data with all of the judges scores then just loop it with parameters on php to get the other candidates data something like

php loop start
   <td>name</td> 
   <td>judge1 score</td>
   <td>judge2 score</td> 
php end loop

or if i could get the whole candidates table with judges score much better for me not to loop them per candidate

I've tried to research similar questions like

Concatenate more than two tables horizontally in SQL Server

I've tried to code myself but I got stuck with joining the judges..

SELECT s.sco_id,c.Name,c.Municipalities 
FROM `tbl_scoring` s 
LEFT JOIN tbl_candidates c ON c.`can_id` = s.`can_id` 
WHERE s.can_id = 11 
  AND crit_id = 1 
ORDER BY s.jud_id asc

I need a query that would generate dynamically depending on the number of judges either get candidate data with scores of judge then loop it on php or much way better if i get all the data without looping

do you want all candidates to appear, even if they have no score for the criteria? do you want all judges to appear, even if they have no score for the criteria? — ysth, Jul 05 '19 at 23:09

HTMHell · Accepted Answer · 2021-06-11T08:46:22.927

Initialize the following arrays:

$judges = [];
$scores = [];
$candidates = [];

Then execute your query, and loop the results. Set those values for each iteration:

$judges[$row['jud_id']] = 1;
$candidates[$row['can_id']] = $row['Name'];
$scores[$row['can_id']][$row['jud_id']] = $row['score'];

Now you want to get the participant judges names, so let's run a SQL query:

$sql = 'SELECT Name FROM judges WHERE id IN (' . implode(',', array_keys($judges)) . ')';

And on every iteration set the judge's name in the $judges array:

$judges[$row['id']] = $row['Name'];

Then for the output:

echo '<tr>';
echo '<td>Name</td>';
ksort($judges);
foreach ($judges as $name) {
    echo '<td>Judge: ' . $name . '</td>';
}
echo '</tr>';

foreach ($scores as $candidateId => $data) {
    echo '<tr>';
    echo "<td>$candidates[$candidateId]</td>";
    ksort($data);
    foreach ($data as $score) {
        echo "<td>$score</td>";
    }
    echo '</tr>';
}

I used ksort on $judges and $data so the score will fit each judge.

score 1 · Answer 2 · answered Jul 06 '19 at 00:18

first, we retrieve the judges' ids and name that exist on the score table.

$judges = [];
$query = "SELECT id, name FROM Judges WHERE id IN ( SELECT DISTINCT jud_id FROM Score )";
// execute the query and store the results in the $judges array.

we retrieve the candidates' ids and name that exist on the score table.

$candidates = [];
$query = "SELECT id FROM Candidate WHERE id IN ( SELECT DISTINCT can_id FROM Score )";
// execute the query and store the results in the $candidates array.

then, we join the candidate and score table.

$candidate_score = [];
$query = "SELECT Candidate.name, Candidate.id as candidate_id , Score.jud_id, Score.score FROM Candidate JOIN Score ON Score.can_id = Candidate.id";
// execute the query and store it in the $candidate_score array.

now, for each candidate, we fill its score on the $score_board array.

$score_board = [];

foreach ( $candidates as $candidat )
{
   $score_board[$candidat] = [];

   foreach ( $judges as $judge )
   {
      $judge_name = $judge['name'];
      $judge_id = $judge['id'];
      $score_board[$candidat][$judge_name] = get_judge_score($candidate_score,$candidat,$judge_id);
   } 
}

this is how the get_judge_score will work:

function get_judge_score ( $scores , $candidate , $judge )
{
   $score_filtred = array_filter($scores, function ($score) use ($candidate,$judge) {
      return $score['jud_id'] == $judge && $score['candidate_id'] = $candidate;
   });

   return count($score_filtred) > 0 ? $score_filtred[0]['score'] : 0;
}

Join tables horizontally and dynamically

2 Answers2