Trouble joining tables

Question

I am trying to make an SQL query that joins three tables and lists a value from two of them.

My current setup is the following:

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

The harddrives have a unique ID an an associated computer linked by ComputerId

| HardDisk(Id)  | HardDisk(ComputerId) |
|----           |-------------|
| 1             | 1           |
| 2             | 2           |
| 3             | 3           |
| 4             | 4           |
| 5             | 5           |
| 6             | 6           |
| 7             | 6           |
| 8             | 7           |
| 9             | 8           |
| 10            | 9           |

Disks have log associated with them. These logs have a unique Id and a HardDiskId which is the disk that the log is associated with

| DiskLog(Id)   | DiskLog(HardDiskId) |
|----           |------------|
| 1             | 1          |
| 2             | 2          |
| 3             | 3          |
| 4             | 4          |
| 5             | 5          |
| 6             | 7          |
| 7             | 6          |
| 8             | 8          |
| 9             | 9          |
| 10            | 10         |

The output I'm looking for is this:

| Computer(Id) | HardDisk(Id) / DiskLog(Id) |
|------------|------------|
| 1          | 1 /1          |
| 2          | 2 / 2         |
| 3          | 3 / 3         |
| 4          | 4 / 4         |
| 5          | 5 / 5         |
| 6          | 7,6 / 6,7     |
| 7          | 8  /  8       |
| 8          | 9   / 9       |
| 9          | 10   / 10     |

I'm trying to get the ComputerId and the associated HardDisk which is found through the DiskLog to be outputted together

I currently have the following query, but it is not working as intended:

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

Answer 1

Consider the following query:

SELECT
    c.Id AS ComputerId,
    CONCAT(GROUP_CONCAT(DISTINCT h.Id),
            ' / ',
           GROUP_CONCAT(d.Id)) AS hd_disk_ids
FROM Computer c
LEFT JOIN HardDisk h ON c.Id = h.ComputerId 
LEFT JOIN DiskLog d ON d.HardDiskId = h.Id
GROUP BY c.Id;

Demo