Hi,

I could use a little help with a db2 query.

create table mainTable (
id integer
);

create table table1 (
id integer,
start_date date
);

create table table1Child(
table1Id integer,
mainTableId integer
);

insert into mainTable values (1);
insert into mainTable values (2);

insert into table1 values (1, date('2009-01-02'));
insert into table1 values (2, date('2009-01-03'));

insert into table1Child values (1, 1);
insert into table1Child values (1, 2);
insert into table1Child values (2, 2);

I would like to do a query such that the result is:

mainTable.id, table1.start_date
1, 2009-01-02
2, 2009-01-03

That is I get the records from the mainTable joined with table1 and I only get the records from mainTable that have the latest start_date. The mainTable.id must only appear once in the result.

Any help would be appreciated.


regards,
Jannic

Hope this may...

db2 "select a.id, b.start_date from maintable a, table1 b where a.id=b.id"

-U

I think the solution might incorporate the MAX() function and a GROUP BY...

Jannic

Your sample data was so simple that I couldn't understand your requirement.

Because, I could make a query to get your sample result without seeing any table other than table1.
Here is an example
(This must not be your requered query!):
My question is that all mainTableId in table1Child are in mainTable?
If answer is yes, then you need not to see mainTable.
So, I assumed that some mainTableId in table1Child may be not in mainTable.

For example:

mainTable:
1d
1
3
4

table1:
id start_date
10 '2009-01-02'
20 '2009-01-03'

table1Child:
table1Id mainTableId
10 1
10 2
10 3
10 4
10 5
20 2
20 3

Expected result:
mainTable.id, table1.start_date
4, 2009-01-02
3, 2009-01-03

Then, a query may be:
If the data or result was defferent from your requirement,
please give me enough sample data and expected result.