Re: Need to query 4 tables....3 are 'subordinates'...(need outer join?)

techguy_chicago_at_yahoo.com (Bomb Diggy) wrote in news:94599bb3.0210022007.1fef3cdd_at_posting.google.com:

> This is a database/app design question as best I can figure.

Hmmm, as near as I can tell, you're going down a rat hole with the design. I'd suggest, if it's not too late to create a surrogate key on each table and build a UNIQUE index on the natural key to preserve the business logic. The surrogate key should follow a naming standard, such as, 'id' :)

For instance, the following is an example of a hub table (t1) and two children:

  [t1] -> [t2]
   |
   v
  [t2]

t1

   id
   ...

t2

   id
   t1_id

t2
  id
  t1_id

Note that it's easy for a developer to know how to join t1 to t2:

     FROM t1, t2 WHERE t1.id = t2.t1_id

Also, your developers should learn SQL and actually, they should learn how to write stored procedures. There should be _no_ raw SQL in the middle-ware. Check out the white paper that discusses all the reasons to use stored procedures:

    http://www.hpdbe.com/white_papers/index.html

-- 
Pablo Sanchez, High-Performance Database Engineering
http://www.hpdbe.com