'GROUP_CONCAT should not include its current value

Current Query I am trying to get names of sibling record by parent UiniqId

SELECT 
    `SubApplicants`.`SubAppId` AS `AppId`,
    `SubApplicants`.`SubApplicationId` AS `ApplicationId`,
    `SubApplicants`.`SubFirstName`,
    `SubApplicants`.`SubSurname`,
    GROUP_CONCAT(CONCAT(`SubApplicants`.`SubFirstName`,
                ' ',
                `SubApplicants`.`SubSurname`)
        SEPARATOR ', ') AS `SubCoApplicantsNames`
   
FROM
    `SubApplicants`
WHERE
    (`SubApplicants`.`IsSubGuarantor` = 0 )
GROUP BY  `SubApplicants`.`SubApplicationId`
mysqlmysql-workbench


Solution 1:[1]

The Question is not exact as you show the output ("current result") that is not groupped by ApplicationId as @forpas also noticed and so it does not represent your real situation. However if I understood you properly you may try this as the code will give you the desired output.

SELECT Applicants.Id AS Id, 
    Applicants.ApplicationId AS ApplicationId, 
    Applicants.FirstName, 
    Applicants.Surname, 
    GROUP_CONCAT(CONCAT (
            Applicants2.FirstName, 
            ' ', 
            Applicants2.Surname
            ) SEPARATOR ', ') AS CoApplicantsNames
FROM Applicants
INNER JOIN Applicants AS Applicants2 ON Applicants.ApplicationId = Applicants2.ApplicationID
WHERE Applicants.FirstName != Applicants2.FirstName
    OR Applicants.Surname != Applicants2.Surname
GROUP BY Applicants2.ApplicationId;

The first FROM will give you the lust of all possible applications and applicants. You may additionally filter it as you wish (for instance (Applicants.IsGuarantor = 0 ) . The joined table will provide you the last group-concatinated field of co-applicants.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions