1

¡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!

1
  • 1
    [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. Sep 27, 2020 at 23:14

1 Answer 1

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

1
  • I don't think 8.0 is required.
    – Rick James
    Sep 28, 2020 at 17:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.