After syntax errors cleared up
Population defined by WHERE clause
- Query runs; results wrong
- Join problems
- Know data: count, characterize population
Adresses for people with addresses:
People with adresses:
Addresses of all 10 people:
select count(*) "count",
count(distinct name_id) "dist"
where name_seriesno = name_now(name_id);
Numbers should match
If they don't, step through the joins:
- Add one join at a time
- Test population again
- Join problem located when numbers don't match
- Population loss; "falling through join"
Ex: From 6 to 5 after adding join
- Population gain: "mulplying through a join"
Ex: 6 and 7: one item counted twice
- 5 and 7: both loss and gain
- Identify row(s) causing problem
- Compare to join conditions
- Determine cause