Combine Multiple Rows Results From Left Join in a unique cell

Question

¡Hi guys!, I don't have any idea how to create a new column that contains all information of results from a Left Join if exists the value

Example

Table 1

--------------------------------------
| FieldID | FieldName | FieldAccount |
|------------------------------------|
| 01 | Jon | 12345 |
--------------------------------------

Table 2

--------------------------------------
| FieldID | FieldName | FieldAccount |
|------------------------------------|
| 01 | Jon | 12345 |
|------------------------------------|
| 02 | Charlie | 12345 |
--------------------------------------

Result

From Table 1
-----------------------------------------------
| FieldID | FieldName | FieldAccount | LookAt |
|---------------------------------------------|
| 01 | Jon | 12345 | 01,02 |
-----------------------------------------------

Field LookAt contains the ID(s) where the FieldAccount matches

Thanks and regards to everyone!

• ComputerVersteher: [ms-access] tag is confusing. You use it as frontend (then remove tag) or do you need a solution for that db too? The provided Group_Concat solution won't work as that function is missing on ms access.

Answer 1

You can use a subquery and a GROUP_CONCAT for that

Schema (MySQL v8.0)

CREATE TABLE Table1 (
 `FieldID` INTEGER,
 `FieldName` VARCHAR(3),
 `FieldAccount` INTEGER
);
INSERT INTO Table1
 (`FieldID`, `FieldName`, `FieldAccount`)
VALUES
 ('01', 'Jon', '12345');
CREATE TABLE Table2 (
 `FieldID` INTEGER,
 `FieldName` VARCHAR(7),
 `FieldAccount` INTEGER
);
INSERT INTO Table2
 (`FieldID`, `FieldName`, `FieldAccount`)
VALUES
 ('01', 'Jon', '12345'),
 ('02', 'Charlie', '12345');

---

Query #1

SELECT
t1.*
,(SELECT GROUP_CONCAT(`FieldID`) FROM Table2 WHERE `FieldAccount` = t1.`FieldAccount`
) 'LookAt '
FROM Table1 t1;
| FieldID | FieldName | FieldAccount | LookAt |
| ------- | --------- | ------------ | ------- |
| 1 | Jon | 12345 | 1,2 |

---

View on DB Fiddle

• Rick James: I don't think 8.0 is required.