Strategy for avoiding a common sql development error (misleading result on join bug) -

Sometimes when I'm writing a little complex selection statement with some joints, the wrong key column is sometimes combined statement Used to still return the results that are valid.

Since the auto numbering value (initially in development in particular) decreases in all the same categories (sub 100s or so) SELECT Sill generates some results. These results are often found to be valid at first glance and a problem is not detected, much later, making debugging more difficult has been made familiar with the data structures and code. (Dev's mind got disturbed.)

I have spent many hours on another issue which I have run several times before. I carefully designate my tables and columns, write my SQL methods in practice, but this is an issue that I do not think it is to avoid competition. It comes back and bites me on average twice a year for productivity.

My question is this: Does anyone have to come in a clever way to avoid this? What do I think is a normal SQL bug / mistake?

I have thought of trying an auto-number to start with the initial values, but it feels silent and tries to keep such a plan straight for the data. Models with dozens of tables ... any better ideas?

PS

I am very careful and systematic in the name of my tables and columns, get Patti ITID column in the patient table, facility facility etc. is. This problem is a habit of raising when it is included in the tables included, where the link takes the additional meaning such as related PatentID, Refining Patients ID, Favorite ITMID etc.

Try to limit the result to a record when typing the long complex SELECT statement For example, the value Take that you have this huge huge CMS system and you have to write internal reports because the report that comes with it is very awesome. You see that there are about 500 tables. Your selection statement is included in 30 of these tables. Your result should be limited by using a WHERE block to your line number.

I recommend that all these code be written and normalized for all cases, break the problem and use WHERE and limit the count of the line only say a record Check all the fields, if they If you look ok, break it and return more code to your code. You should make normal only after further investigation.

It bites much of us which keeps adding to it as long as it does not look right, but after the blau accountant runs the report, it shows that for 4 lakhs P.O. In fact the telephone bill for the whole year was messed up in any way!