You have a couple of options, Thomas, I'd go with

select nm.nh_main_id,
max(coalesce(max(st1.MyDate), 0), coalesce(max(st2.MyDate), 0))
from nh_main nm
left join subtable1 st1 on mn.nh_main_id = st1.nh_main_id
left join subtable2 st2 on mn.nh_main_id = st2.nh_main_id
where ...
group by 1

(Hmm, I doubt the innermost max works, maybe you need views with each
subtable to find the max value and then do the 'outer select' similar to
what is above to get the max MyDate and decent performance)...

You probably need two indexes on each subtable (or one combined index,
which will be slightly faster), one for nm_main_id and the other a
DESCENDING index on MyDate.

Also, note that I wrote 'where ...'. You don't want to select 5 million
rows, normally you're only interested in a small subset (I guess that
normal, ad-hoc queries would return less than 100 rows, more than one
million rows are virtually only ever required for export/import).

HTH,
Set

Thomas Besand wrote:

> 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
> of the (sub)tables (SELECT MAX(MYDATE) FROM SUBTABLE?? WHERE NH_MAIN_ID
> = :ipMAINID)
>
>
> Thanks for any help,
> have a nice day,
>
> Thomas