0

I have a table which outputs all the sql results from Table 1. Its primary column (stationName) has all unique names. I have added columns to this new table to also include results (scores) from Table 2. The below query yields results which are spread across new lines, creating duplicates of the same name.

$query =   "SELECT * FROM table1 
            LEFT JOIN table2 ON table1.stationName = table2.stationName";

$stationsList = mysqli_query($link, $query);


<?php while($row = mysqli_fetch_array($stationsList)):;?>

<tr>
<td><?php echo $row[table1ItemA];?></td>
<td><?php echo $row[table1ItemB];?></td>
<td><?php echo $row[table1ItemC];?></td>
<td><?php echo $row[table1ItemD];?></td>
<td><?php echo $row[table2score1];?></td>
<td><?php echo $row[table2score2];?></td>
<td><?php echo $row[table2score3];?></td>
</tr>

<?php endwhile;?>

Outputs the score columns as:

Name A Score 1

Name A .............Score 2

Name A ............................Score 3

What I would like to achieve is:


Name A Score 1, Score 2, Score 3.

GROUP BY does not work, as it returns only one score for the three columns.

Doing some research, I believe I need to use DISTINCT.

However, I still need to be able to SELECT * from table1, or at least be able to include all of it in the combined table.

Can anyone advise on the best way to achieve this please?

Edit:

To better show the two tables and what I would like to achieve combined.

Table 1 (Fixed Info)

+-------------+------------+---------+
| StationName |  Address   | Manager |
+-------------+------------+---------+
| Station1    | London     | John    |
| Station2    | Liverpool  | Phil    |
| Station3    | Manchester | Mike    |
+-------------+------------+---------+

Table 2 (Varies Monthly)

+-------------+--------+--------+--------+
| StationName | Score1 | Score2 | Score3 |
+-------------+--------+--------+--------+
| Station1    | Pass   | Fail   | Pass   |
| Station2    | Fail   | Pass   | Pass   |
| Station3    | Pass   | Pass   | Pass   |
+-------------+--------+--------+--------+

Combined Table as I would like it to appear:

+-------------+------------+---------+--------+--------+--------+
| StationName |  Address   | Manager | Score1 | Score2 | Score3 |
+-------------+------------+---------+--------+--------+--------+
| Station1    | London     | John    | Pass   | Fail   | Pass   |
| Station2    | Liverpool  | Phil    | Fail   | Pass   | Pass   |
| Station3    | Manchester | Mike    | Pass   | Pass   | Pass   |
+-------------+------------+---------+--------+--------+--------+

How it appears:

+-------------+------------+---------+--------+--------+--------+
| StationName |  Address   | Manager | Score1 | Score2 | Score3 |
+-------------+------------+---------+--------+--------+--------+
| Station1    | London     | John    | Pass   |        |        |
| Station1    | London     | John    |        | Fail   |        |
| Station1    | London     | John    |        |        | Pass   |
| Station2    | Liverpool  | Phil    | Fail   |        |        |
| Station2    | Liverpool  | Phil    |        | Pass   |        |
| Station2    | Liverpool  | Phil    |        |        | Pass   |
| Station3    | Manchester | Mike    | Pass   |        |        |
| Station3    | Manchester | Mike    |        | Pass   |        |
| Station3    | Manchester | Mike    |        |        | Pass   |
+-------------+------------+---------+--------+--------+--------+
misterpauly
  • 35
  • 5
  • First try removing the `;` from this line `` – RiggsFolly Sep 22 '16 at 19:58
  • You need to pivot table 2 to get all the scores for the same player on the same row. See the duplicate question for how to do this. Then join that with table 1 to get the player information. – Barmar Sep 22 '16 at 20:00
  • So to help you troubleshoot this we don't need the PHP code we need the SQL statement and examples of data from the 2 tables and a desired result set, in this case also post what you are getting. It seems like you have an issue in your join or multiple rows meet the join condition or.... – Matt Sep 22 '16 at 20:03
  • Have updated to show examples of data and what I would like to see. – misterpauly Sep 22 '16 at 20:24

0 Answers0