OUTER JOIN problem

[Nikolaus Rumm]

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 ?

[Knut Stolze]

Nikolaus Rumm wrote:

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 ?

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