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!

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