mysql - Issue concatenating rows with duplicates

Question

I have run into some issues trying to combine a row of variables where dublicates can be found.

Computers with Ids are saved in the Computer table:

| Computer.Id |
|-------------|
| 1           |
| 2           |
| 3           |
| 4           |
| 5           |
| 6           |
| 7           |
| 8           |
| 9           |

Harddrive are saved in a HardDisk table with a HardDisk Id exclusive to the harddrive and a ComputerId linked to the Id in the Computer table

| Harddisk.ComputerId | Harddisk.Id |
|---------------------|-------------|
| 1                   | 1           |
| 2                   | 2           |
| 3                   | 3           |
| 4                   | 4           |
| 5                   | 5           |
| 6                   | 6           |
| 6                   | 7           |
| 7                   | 8           |
| 8                   | 9           |
| 9                   | 10          |

The output I am looking to achieve is:

| Harddisk.ComputerId | Harddisk.Id |
|---------------------|-------------|
| 1                   | 1           |
| 2                   | 2           |
| 3                   | 3           |
| 4                   | 4           |
| 5                   | 5           |
| 6                   | 6,7         |
| 7                   | 8           |
| 8                   | 9           |
| 9                   | 10          |

The output I'm currently getting is:

| Harddisk.ComputerId | Harddisk.Id |
|---------------------|-------------|
| 1                   | 1           |
| 2                   | 2           |
| 3                   | 3           |
| 4                   | 4           |
| 5                   | 5           |
| 6                   | 6           |
| 7                   | 8           |
| 8                   | 9           |
| 9                   | 10          |

Notice how Harddisk 7 which is the disk that shares Computer 6 is gone.

My current query looks like the following, courtesy of scaisEdge:

SELECT *, group_concat(HardDisk.Id)
from Computer
inner join HardDisk on Computer.Id = HardDisk.ComputerId
group by Computer.Id

I hope someone is able to help me out!

question from:https://stackoverflow.com/questions/65941945/issue-concatenating-rows-with-duplicates

Answer 1

You can't use * because this produce an a wrong aggregation in mysql for version < 5.7

try use explicit column's name in select

SELECT computer.ID, group_concat(HardDisk.Id) my_disk
from Computer
inner join HardDisk on Computer.Id = HardDisk.ComputerId
group by Computer.Id 

if you need more column's not related to the same aggreagtion level you need a join

In mysql version < 5.7 if some columns mentioned in select clause are not mentioned properly in group by the aggregation function return the first occurrence of the select and not the correct aggreagted result

try add

 echo  $row['my_disk];