By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,851 Members | 1,746 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,851 IT Pros & Developers. It's quick & easy.

OUTER JOIN problem

P: n/a
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 ?
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.