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

Question

Sometimes when i'm writing moderately complex SELECT statements with a few JOINs, wrong key columns are sometimes used in the JOIN statement that still return valid-looking results.

Because the auto numbering values (especially early in development) all tend to fall in similar ranges (sub 100s or so) the SELECT sill produces some results. These results often look valid at first glance and a problem is not detected until much, much later making debugging much more difficult because familiarity with the data structures and code has staled. (Gone stale in the dev's mind.)

i just spent several hours tracking down yet another of this issue that i've run into a too many times before. i name my tables and columns carefully, write my SQL statements methodically but this is an issue i can't seem to competely avoid. It comes back and bites me for hours of productivity about twice a year on average.

My question is: Has anyone come up with a clever method for avoiding this; what i assume is probably a common SQL bug/mistake?

i have thought of trying to auto-number starting with different start values but this feels cludgy and would get ugly trying to keep such a scheme straight for data models with dozens of tables... Any better ideas?

P.S.

i am very careful and methodical in naming my tables and columns. Patient table gets PatientId column, Facility get a FacilityId etc. This issues tends to arise when there are join tables involved where the linkage takes on extra meaning such as: RelatedPatientId, ReferingPatientId, FavoriteItemId etc.

Answer 1

If you have a visualization or diagramming tool for your SQL statements, you can follow the joins visually, and any errors will become immediately apparent, provided you have followed a sensible naming scheme for your primary and foreign keys.

Answer 2

One option would be to use your natural keys.

More practically, Red Gate SQL Prompt picks the FK columns for me.

I also tend to build up one JOIN at a time to see how things look.

Answer 3

You could use GUIDs as your primary keys, but it has its pros and cons.

This pro is actually not mentioned on that page.

I have never tried doing this myself - I use a tool on top of SQL that makes incorrect joins very unlikely, so I don't have this problem. I just thought I'd mention it as another option though!

Answer 4

1. For IDs use TableNameID, for example for table Person, use PersonID
2. Use db model and look at the drawing when writing queries.

This way join looks like:

... ON p.PersonID = d.PersonID

as opposed to:

... ON p.ID = d.ID

Auto-increment integer PKs are among your best friends.

Answer 5

When writing long complex SELECT statements try to limit the result to one record. For instance, assume you have this gigantic enormous awesome CMS system and you have to write internal reports because the reports that come with it are horrendous. You notice that there are about 500 tables. Your select statement joins 30 of these tables. Your result should limit your row count by using a WHERE clause.

My advice is to rather then get all this code written and generalized for all cases, break the problem up and use WHERE and limit the row count to only say a record. Check all fields, if they look ok, break it up and let your code return more rows. Only after further checking should you generalize.

It bites a lot of us who keep adding more and more joins until it seems to look ok, but only after Joe Blow the accountant runs the report does he realize that the PO for 4 million was really the telephone bill for the entire year. Somehow that join got messed up!

Answer 6

Your column names should take care of this unless you named them all "ID". Are you writing multiple select statement using the same tables? You may want to create views for the more common ones.

Answer 7

If you're using SQL Server, you can use GUID columns as primary keys (that's what we do). You won't have problems with collisions again.