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
