[thelist] SQL help - I think JOINing is required

[rudy]

> Matt, these tables all will have 1-1 relationships. However there
> will be an additional table in the future which may have multiple
> records for a single ID.

hi marc

if they are 1-1 relationships, consider putting all the columns into one
table next time you design tables like this -- there is a performance
tradeoff, but it's probably immaterial unless you have millions of records,
but on the other hand, it's easier to write queries against one table than
four

> How much nastier is "a little"?

it isn't at all -- the 1-m query is identical

it's just harder to deal with the output, typically you'll want to "group"
the output by GenInfo ID

also, you probably want outer rather than inner joins, just in case there's
an unmatched record in one of the tables

for example, if you have

  general  contact  job  education
      g1           c1       j1         e1
      g2           c2       j2         e2
      g3           --         j3         e3
      g4           c4       j4         e4

then the query will return only rows 1, 2, and 4, even though there is a
general record g3

so change your query to

    from GenInfo
      outer join Contact on GenInfo.ID = Contact.ID
      outer join Job on GenInfo.ID = Job.ID
      outer join Education on GenInfo.ID = Education .ID

then if there are any contact, job, or education records missing, you'll
get nulls in the corresponding fields but the row will still be there

by the way, matt, one of the reasons i prefer the above syntax to the table
list syntax is because after i write

    from GenInfo, Contact, Job, Education
      where GenInfo.ID ???

i always have to go look up the syntax for the outer join, i can never
remember where that darned asterisk is supposed to go...

rudy