Subject Question : SQL to span several tables
Author Thomas Besand
Hi everybody,

at a customer's site they're running FB 2.0 on a Win 2003 Server.

They have a db that is about 5 GB large, with some 30 tables in it.
The db stores contact and marketing data.

Now I have the following requirement:

There is a main table, which is in fact called nh_main with some 30
varchar fields and an integer nh_main_id field with a primary key on it.
The table has ~5 million records, expected to be growing to 30 million.
Additionally there are 28 tables (all the same structure) that also have
a nh_main_id field that correlates to the id field in the main table.
These (sub)tables have a primary key field of their own, which is
independent from the nh_main_id. There are also some varchar(8) fields
in those tables that store a date (e.g. 20061206 for today) and a bunch
of other fields.

I want to find the highest value in column MYDATE of one of the
aforementioned (sub)tables, that has a given nh_main_id, and I want to
find it for each entry in the main table.

Can you point me in another direction than the one I'm following now,
and which I consider not very effective:
I open the main table, walk through each record and run a query on each
= :ipMAINID)

Thanks for any help,
have a nice day,


Thomas Besand
Besand EDV & Maschinenwartung
Prinzessinnenstr. 16
10969 Berlin <>
Festnetz: 030- 61 65 66 44
mobil E+: 0178- 20 20 367
mobil DK: +45 -415 85 284

[Non-text portions of this message have been removed]