0

¡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
    [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. – ComputerVersteher Sep 27 at 23:14
0

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

| improve this answer | |
  • I don't think 8.0 is required. – Rick James Sep 28 at 17:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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