Hello,

at first: I am a total SQL idiot.

My problem is as follows:

CREATE TABLE ta_master (
masterid integer not null,
title varchar(20),
constraint idx_master primary key(masterid))

CREATE TABLE ta_detail (
detailid integer not null,
masterid integer,
lastupdate timestamp not null,
constraint idx_detail primary key(detailid))

ALTER TABLE ta_detail ADD CONSTRAINT c_detail_x_master FOREIGN
KEY(masterid) REFERENCES ta_master(masterid)

INSERT INTO ta_master values(1, 'first');
INSERT INTO ta_master values(2, 'second');
INSERT INTO ta_detail values(1, 1, CURRENT TIMESTAMP);
INSERT INTO ta_detail values(2, 1, CURRENT TIMESTAMP);
INSERT INTO ta_detail values(3, null, CURRENT TIMESTAMP);

I want to LEFT OUTER JOIN both tables. Like this:

SELECT * FROM ta_master master
LEFT OUTER JOIN ta_detail detail
ON detail.masterid=master.masterid

This works as expected:
MASTERID TITLE DETAILID MASTERID LASTUPDATE
----------- ---------- ----------- -----------
--------------------------
1 first 1 1
2004-07-26-15.13.37.524746
1 first 2 1
2004-07-26-15.13.40.469674
2 second - - -

Now I want the outer join with only the latest detail record. This
means that I want one result per master with an optional detail record
joined together. The detail is always the most recent one. Like this:

MASTERID TITLE DETAILID MASTERID LASTUPDATE
----------- ---------- ----------- -----------
--------------------------
1 first 2 1
2004-07-26-15.13.40.469674
2 second - - -

How should the query look like ?

Nikolaus Rumm wrote:
[color=blue]
> Hello,
>
> at first: I am a total SQL idiot.
>
> My problem is as follows:
>
> CREATE TABLE ta_master (
> masterid integer not null,
> title varchar(20),
> constraint idx_master primary key(masterid))
>
> CREATE TABLE ta_detail (
> detailid integer not null,
> masterid integer,
> lastupdate timestamp not null,
> constraint idx_detail primary key(detailid))
>
> ALTER TABLE ta_detail ADD CONSTRAINT c_detail_x_master FOREIGN
> KEY(masterid) REFERENCES ta_master(masterid)
>
> INSERT INTO ta_master values(1, 'first');
> INSERT INTO ta_master values(2, 'second');
> INSERT INTO ta_detail values(1, 1, CURRENT TIMESTAMP);
> INSERT INTO ta_detail values(2, 1, CURRENT TIMESTAMP);
> INSERT INTO ta_detail values(3, null, CURRENT TIMESTAMP);
>
> I want to LEFT OUTER JOIN both tables. Like this:
>
> SELECT * FROM ta_master master
> LEFT OUTER JOIN ta_detail detail
> ON detail.masterid=master.masterid
>
> This works as expected:
> MASTERID TITLE DETAILID MASTERID LASTUPDATE
> ----------- ---------- ----------- -----------
> --------------------------
> 1 first 1 1
> 2004-07-26-15.13.37.524746
> 1 first 2 1
> 2004-07-26-15.13.40.469674
> 2 second - - -
>
> Now I want the outer join with only the latest detail record. This
> means that I want one result per master with an optional detail record
> joined together. The detail is always the most recent one. Like this:
>
> MASTERID TITLE DETAILID MASTERID LASTUPDATE
> ----------- ---------- ----------- -----------
> --------------------------
> 1 first 2 1
> 2004-07-26-15.13.40.469674
> 2 second - - -
>
> How should the query look like ?[/color]

Basically, you want to find only those rows in TA_DETAIL which have the
largest (most recent) timestamp for a certain MASTERID, right? So you can
simply express the query in those terms:

SELECT *
FROM ta_master master LEFT OUTER JOIN
( SELECT masterid, detailid, timestamp
FROM ta_detail AS d1
WHERE timestamp >= ALL ( SELECT d2.timestamp
FROM ta_detail AS d2
WHERE d1.masterid = d2.masterid ) )
AS detail ON
detail.masterid = master.masterid

or you filter the resulting rows of the join:

SELECT *
FROM ta_master master LEFT OUTER JOIN
ta_detail detail ON
detail.masterid = master.masterid
WHERE NOT EXISTS ( SELECT 1
FROM ta_detail AS d
WHERE d.masterid = master.masterid AND
d.timestamp > detail.timestamp )

Well, something like that... ;-)

--
Knut Stolze
Information Integration
IBM Germany / University of Jena