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`
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;
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.
Answered By – asd-tm
Answer Checked By – Cary Denson (BugsFixing Admin)