Row duplication caused by a join to a non-unique index.
Joins on denormalized data vs. joins on unique code lists.
Semi-random updates: This is where there is one record to be updated but the SQL statement specifying the value to be used in the update returns multiple values. Obviously only one value can be used to update one column of one row.
Omission of data due to inner joins.
SELECT *
FROM t1
INNER JOIN t2 ON t1.fk2 = t2.pk2
INNER JOIN t3 ON t2.fk3 = t3.pk3
The number of records being returned is first determined by the inner join between t1 and t2 however a join between t1 and t3 may return a different number of records so if we join t3 on t2 we may end up ommiting data.
Instead we should join t3 on t1 as shown in the following example.
SELECT *
FROM t1
INNER JOIN t2 ON t1.fk2 = t2.pk2
INNER JOIN t3 ON t1.fk3 = t3.pk3
A Constrant of SQL: We must also remember to avoid row duplication caused by the following type of joins.
SELECT *
FROM t1
INNER JOIN t2 ON t1.fk1 = t2.pk1
INNER JOIN t3 ON t1.fk1= t3.pk1
As long the joins are one to one we are OK however row duplication will occur if more than one record is linked to more than one other record.
a know constraint with SQL.
Omission of data due to active inactive flags instead of selecting between two dates such as acquired date - disposed date. Selecting an inactive flag in a historica report will omit inactive records so the method will not work to report historical data.
Omission of data by putting where clause criteria on left outer joined table elements in the where clause instead of the join clause.
Omission of data by joining outer or inner joined tables to each other instead of a root data set containing all possible keys for both tables.
Summing unrelated percentages.
Excluding data in calculations due to bad join and where clause criteria.
Joins on denormalized data vs. joins on unique code lists.
Semi-random updates: This is where there is one record to be updated but the SQL statement specifying the value to be used in the update returns multiple values. Obviously only one value can be used to update one column of one row.
Omission of data due to inner joins.
SELECT *
FROM t1
INNER JOIN t2 ON t1.fk2 = t2.pk2
INNER JOIN t3 ON t2.fk3 = t3.pk3
The number of records being returned is first determined by the inner join between t1 and t2 however a join between t1 and t3 may return a different number of records so if we join t3 on t2 we may end up ommiting data.
Instead we should join t3 on t1 as shown in the following example.
SELECT *
FROM t1
INNER JOIN t2 ON t1.fk2 = t2.pk2
INNER JOIN t3 ON t1.fk3 = t3.pk3
A Constrant of SQL: We must also remember to avoid row duplication caused by the following type of joins.
SELECT *
FROM t1
INNER JOIN t2 ON t1.fk1 = t2.pk1
INNER JOIN t3 ON t1.fk1= t3.pk1
As long the joins are one to one we are OK however row duplication will occur if more than one record is linked to more than one other record.
a know constraint with SQL.
Omission of data due to active inactive flags instead of selecting between two dates such as acquired date - disposed date. Selecting an inactive flag in a historica report will omit inactive records so the method will not work to report historical data.
Omission of data by putting where clause criteria on left outer joined table elements in the where clause instead of the join clause.
Omission or potential omission of data due to linking to a view on data coming from a left outer join within the view. In general joins between views should be done from values in the base tables otherwise data will potentially be omitted.
Omission of data by joining outer or inner joined tables to each other instead of a root data set containing all possible keys for both tables.
Summing unrelated percentages.
Aggregating distinct values.
select DISTINCT
isnull(amount,0) as TotAmt
, PERIOD
from table1
GROUP BY PERIOD
GROUP BY PERIOD
Excluding data in calculations due to bad join and where clause criteria.
