How to combine rows after a JOIN where duplicate first columns occur, but differing information in the other columns

Databases
1

I am having a problem with the way the results are outputted to a HTML table after combining the data of two sql tables.

Table 1 appears as below

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

Table 2 appears as below

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

I would like the combined data to output as shown in this table

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

However it appears like this.

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

My query is:

SELECT * 
FROM table1 
LEFT JOIN table2 
ON table1.stationName = table2.stationName

I guess I am looking for a way of using something similar to GROUP BY, but without it aggregating the data.

Someone suggested pivot tables, though from what I have read of those, they are useful for calculations - I am interested in combining text data into the columns from multiple tables where the common link is the station name.

Can anyone please advise on how this can be achieved?

2

SELECT StationName , Address , Manager , MAX(Score1) AS Score1 , MAX(Score2) AS Score2 , MAX(Score3) AS Score3 FROM table1 LEFT OUTER JOIN table2 ON table2.stationName = table1.stationName GROUP BY StationName , Address , Manager

1 Like
3

Many thanks, works great!

4

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.