Parental Phone number - queries


Today when I run the query for “Parental telephone list” , it is giving the result of both parent 1 and parent 2 having parent 2’s phone number even though parent 1 has registered with different phone number. So here after the query has been fixed it gives us the correct result.

SELECT student.surname AS studentSurname, student.preferredName AS studentpreferredName, as family, as rollGroup, parent1.title AS parent1Title, parent1.surname AS parent1Surname, parent1.preferredName AS parent1PreferredName, CONCAT(parent1.phone1Type,’ ‘,parent1.phone1CountryCode,’ ‘, parent1.phone1) AS p1phone1, CONCAT(parent1.phone2Type,’ ‘,parent1.phone2CountryCode,’ ‘, parent1.phone2) AS p1phone2, CONCAT(parent1.phone3Type,’ ‘,parent1.phone3CountryCode,’ ‘, parent1.phone3) AS p1phone3, CONCAT(parent1.phone4Type,’ ‘,parent1.phone4CountryCode,’ ‘, parent1.phone4) AS p1phone4, parent2.title AS parent2Title, parent2.surname AS parent2Surname, parent2.preferredName AS parent2PreferredName, CONCAT(parent2.phone1Type,’ ‘,parent2.phone1CountryCode,’ ‘, parent2.phone1) AS p2phone1, CONCAT(parent2.phone2Type,’ ‘,parent2.phone2CountryCode,’ ‘, parent2.phone2) AS p2phone2, CONCAT(parent2.phone3Type,’ ‘,parent2.phone3CountryCode,’ ‘, parent2.phone3) AS p2phone3, CONCAT(parent2.phone4Type,’ ‘,parent2.phone4CountryCode,’ ', parent2.phone4) AS p2phone4 FROM gibbonPerson AS student
JOIN gibbonStudentEnrolment ON (student.gibbonPersonID=gibbonStudentEnrolment.gibbonPersonID)
JOIN gibbonRollGroup ON (gibbonRollGroup.gibbonRollGroupID=gibbonStudentEnrolment.gibbonRollGroupID)
LEFT JOIN gibbonFamilyChild ON (gibbonFamilyChild.gibbonPersonID=student.gibbonPersonID)
LEFT JOIN gibbonFamily ON (gibbonFamilyChild.gibbonFamilyID=gibbonFamily.gibbonFamilyID)
LEFT JOIN gibbonFamilyAdult AS parent1Fam ON (parent1Fam.gibbonFamilyID=gibbonFamily.gibbonFamilyID AND parent1Fam.contactPriority=1)
LEFT JOIN gibbonPerson AS parent1 ON (parent1Fam.gibbonPersonID=parent1.gibbonPersonID AND parent1.status=‘Full’)
LEFT JOIN gibbonFamilyAdult AS parent2Fam ON (parent2Fam.gibbonFamilyID=gibbonFamily.gibbonFamilyID AND parent2Fam.contactPriority=2)
LEFT JOIN gibbonPerson AS parent2 ON (parent2Fam.gibbonPersonID=parent2.gibbonPersonID AND parent2.status=‘Full’)
WHERE gibbonStudentEnrolment.gibbonSchoolYearID=(SELECT gibbonSchoolYearID FROM gibbonSchoolYear WHERE status=‘Current’) AND student.status=‘Full’ AND NOT (parent2.surname IS NULL AND parent2Fam.contactPriority IS NOT NULL) AND NOT (parent1.surname IS NULL AND parent1Fam.contactPriority IS NOT NULL);

Ayu, well spotted, and thanks for providing the solution. I see now where I went wrong in accidentally reusing the same column name for parent 1 and parent 2 phone numbers. I’ve updated Query Builder, so if you resync your queries you’ll get the fix. Thanks : ) Ross.