Example 1
It is possible to have duplicate names. For example, if you do not select StudentContactType as SC1, you only pick up the primary contacts and not Term and Alternate as well. Otherwise, you would be returning one line for every contact that a student has.
Query with duplicate records |
|||
SELECT ID, StudentContactMailName, StudentContactAddressComma, StudentContactType FROM vStudentContactAddress WHERE CurrentSemesterOnlyFlag = 1 |
|||
Returns |
|||
ID |
ContactMailName |
ContactAddress |
ContactType |
364 |
Mr Joe Black |
15 Green St, MT WAVERLEY VIC 3149 |
SC1 |
364 |
Mrs Jane Black |
341 Flowerdale Ave, St Kilda VIC 3182 |
SC2 |
Query with correction |
|||
SELECT ID, StudentContactMailName, StudentContactAddressComma, StudentContactType FROM vStudentContactAddress, StudentContact WHERE CurrentSemesterOnlyFlag = 1 AND NormalMailFlag = 1 |
|||
Returns |
|||
ID |
ContactMailName |
ContactAddress |
ContactType |
364 |
Mr Joe Black |
15 Green St, MT WAVERLEY VIC 3149 |
SC1 |
Example 2
Another cause of duplication in a student contact query comes from the selection of any student fields.
For example, you want to mail out one letter to the parents of students, regardless of whether they have multiple siblings at the school. You also decide that you want to sort by YearLevel so you use this field as well.
To adjust the query to avoid duplications because siblings may be in different year levels, you need to add the:
Query with correction |
||
SELECT DISTINCT StudentContactMailName, StudentContactAddressComma, YearLevel FROM vStudentContactAddress WHERE StudentContactNormalMailFlag = 1 AND CurrentSemesterOnlyFlag = 1 |
||
Returns |
||
ContactMailName |
ContactAddress |
YearLevel |
Mr Joe Black |
15 Green St, MT WAVERLEY VIC 3149 |
5 |
Mr Joe Black |
15 Green St, MT WAVERLEY VIC 3149 |
10 |
Last modified: 26/05/2017 5:13:59 PM
© 2017 Synergetic Management Systems. Published 20 July 2017.