SQL Child / Lining Tables Up

The Quiet Multiplication That Doubles Your Totals

When the value you match on repeats, records combine with each other and your result grows without any warning.

Here is the trap, and it catches experienced people too. Suppose a customer has three recorded addresses, all sitting in an address collection under the same customer identifier. You line your orders up against that collection to attach a suburb to each order. Each order now finds three matching address records instead of one, so each order produces three lines. Your list of two hundred orders has become six hundred lines. No error appears. Nothing is broken. The system did exactly what you described, which was to pair every order with every matching address, and there were three of them.

This would be merely untidy if you stopped there, because a human scanning the list would probably notice the repetition. The damage comes when you immediately summarise. You total the order values across this expanded list and get three times the real revenue, and now it is a single number with no visible repetition in it at all. It looks like a total. It is a total. It is just a total of a set that contains each order three times, and there is nothing about the figure itself that could possibly tell you so.

The defence is boringly simple and you should apply it every single time. Count your records before you combine, count them after, and be able to explain the difference. If you had two hundred orders and you now have six hundred lines, you must be able to say why, in words, before you go any further. Sometimes the growth is legitimate and expected. Usually it is not. Either way, the moment you make that count a reflex rather than an afterthought, this entire category of error stops happening to you.