Common duplication problems
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 | ||||||||||||
Returns | ||||||||||||
|
Query with correction | ||||||||
|---|---|---|---|---|---|---|---|---|
SELECT ID, StudentContactMailName, StudentContactAddressComma, StudentContactType | ||||||||
| Returns | ||||||||
|
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:
- YearLevel field to the query
key word DISTINCT.
| Query with correction | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| SELECT DISTINCT StudentContactMailName, StudentContactAddressComma, YearLevel FROM vStudentContactAddress WHERE StudentContactNormalMailFlag = 1 AND CurrentSemesterOnlyFlag = 1 | |||||||||
| Returns | |||||||||
|