[FIXED] How can I create a Group_Concat field per table using values from other records

Issue

Suppose a table of First and Last Names and for each record I want to do comma-delimited list of relatives.

• 1ST | LAST | RELATIVES
• Bob | Smith | Alice,Andrew
• Alice | Smith | Bob,Andrew
• Andrew |Smith | Bob,Alice
• Alex | Jones | Anny, Ricky
• Anny | Jones | Alex, Ricky
• Ricky | Jones | Alex, Anny

As per this sqlFiddle

http://sqlfiddle.com/#!9/25d80c/1

I know how to group_contact manually for any last name but am unclear how for each record I could have it go find the records with matching last name and run the same group_concat

Solution

You can do it with a self LEFT join and aggregation:

SELECT s1.First, s1.Last,
       GROUP_CONCAT(s2.First) Relatives
FROM Surnames s1 LEFT JOIN Surnames s2
ON s2.Last = s1.Last AND s2.First <> s1.First
GROUP BY s1.First, s1.Last;

See the demo.

Answered By - forpas

Answer Checked By - David Goodson (PHPFixing Volunteer)